UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Create Module And Call From Select Query, Access 2010    
 
   
MikeWaring1
post Aug 9 2018, 08:16 AM
Post#1



Posts: 93
Joined: 7-November 12



Hi, I'm trying to have a first bash at VBA (I've always stayed clear as coding is a foreign language to me LOL, but I've tried to bite the bullet and have a crack).

I've written some code into a new module which saved OK, but I'm stuck on how to now call this from a query. I've tried creating a new field [Parcel WT], and using the build process to call this module.

However, although the module appears when I go to Expression Builder --> Functions --> (name of the DB)-->Expression Categories, (I've named the Module [Securedmail_Parcel_Type), it just won't allow the module to be selected and put into the Expression Builder section.

I'm stuck now - I don't know if I've created the VBA module correctly, or what I'm doing wrong. pullhair.gif

Here's the VBA Module code:

CODE
Option Compare Database
Option Explicit

Select Case [Parcel Wt]

Case 0.01 To 0.748
[UK Ship Method] "LL"

Case 0.75 To 0.2
[UK Ship Method] "RM2"

Case 2.001 To 5
[UK Ship Method] "BAG"

Case 5.001 To 30
[UK Ship Method] "BOX"

Case Else
[UK Ship Method] "HVY"



I suspect that something might be missing from the VBA code, but I haven't go a clue what
Can anyone help me???

Kindest regards
Mike
Go to the top of the page
 
GroverParkGeorge
post Aug 9 2018, 08:58 AM
Post#2


UA Admin
Posts: 33,936
Joined: 20-June 02
From: Newcastle, WA


That is not a valid procedure in VBA.

Modules are containers that contain individual procedures. Procedures can be Subs or Functions.

You execute only Subs or Functions, not the module in which they reside.

Here's what your code "MIGHT" look like, properly written.



CODE
Option Compare Database
Option Explicit

Public Function UKShipMethod(bval dblParcelWt As Double ) as String

Select Case dblParcelWt
    Case 0.01 To 0.748
        UKShipMethod = "LL"
    Case 0.75 To 0.2
        UKShipMethod ="RM2"
    Case 2.001 To 5
        UKShipMethod = "BAG"
    Case 5.001 To 30
        UKShipMethod = "BOX"
    Case Else
        UKShipMethod = "HVY"
    End Select

End Function


In your query, use it like this:
SQL
SELECT UKShipMethod([Parcel Wt]) as ShipMethod
FROM tblYourTableNameGoesHere

Go to the top of the page
 
MikeWaring1
post Aug 10 2018, 03:55 AM
Post#3



Posts: 93
Joined: 7-November 12



Hi George, thanks for your help and advice.

I pasted your VBA code into my module but the line
CODE
Public Function UKShipMethod(bval dblParcelWt As Double ) as String
is highlighted in red, although it allows me to save the module.

I also pasted the SQL code into the query after first clearing out all the original code, but I get a "Compile Error. In query Expression UKhipmethod([Parcel Wt]" For some reason the last curved bracket is missing from the error message - I'm not sure if this is significant?

Any ideas on how to fix this?

The actual field names in question are [UK Ship Method] and [Parcel Wt] and the underlying table is [tbl_SECURED MAIL Post label CSV]

Kindest regards
Mike
Go to the top of the page
 
JonSmith
post Aug 10 2018, 04:00 AM
Post#4



Posts: 3,956
Joined: 19-October 10



CODE
bval dblParcelWt


Thats a easy spelling mistake.

It should be
CODE
ByVal dblParcelWt
Go to the top of the page
 
GroverParkGeorge
post Aug 10 2018, 06:23 AM
Post#5


UA Admin
Posts: 33,936
Joined: 20-June 02
From: Newcastle, WA


Thanks for the catch.

Change the sample names I made up for your real names.

Best of luck with the project.
Go to the top of the page
 
MikeWaring1
post Aug 10 2018, 06:47 AM
Post#6



Posts: 93
Joined: 7-November 12



Hi George and Jon, thanks very much for your expert inputs - it works perfectly now!

I can see that this CASE function could be very useful for other parts of the project - can it be used on more than one parameter field?

For example, taking this one here, it determines the shipping method type based just on the weight of the parcel, but in reality the shipping method also depends on the size of the parcel, so a certain width and a certain length, height, could also change the ship type.

Or of course it could (and actually will) be a combination of all four parameters - if any of the 4 are outside the max parameters the ship type will change.

Any thoughts on this?

Kindest regards

Mike
Go to the top of the page
 
GroverParkGeorge
post Aug 10 2018, 07:05 AM
Post#7


UA Admin
Posts: 33,936
Joined: 20-June 02
From: Newcastle, WA


Yes, you can set up as many parameters as you need, and you can nest case statements, so you could handle both length and width that way.

However, it sounds like you might be better off with a reference table. It would be more flexible than hard-coded parameters in a case statement.

A reference table would have nine fields: MinWeight, MaxWeight, MinHeight, MaxHeight, MinLength, MaxLength, Minwidth, MaxWidth, and ShipType. Each combination of the parameters would have a value for ShipType. Once set up, that table would be joined into the query to provide the appropriate ShipType. Maintaining such a table is probably less tricky than updating Case Statements in VBA.
Go to the top of the page
 
MikeWaring1
post Aug 10 2018, 09:19 AM
Post#8



Posts: 93
Joined: 7-November 12



Hi George, thnaks for the info. Yes I think that would be an excellent solution - I can easily set up the table, but that's as far as I could go, given my experience of SQL / VBA.

As an example, could you change the below code for me to reflect the table fields? I'm not sure how I tie all these in and how to make it return the correct answer.

Also, what order would I need to put the code in to ensure it evaluates correctly? e.g. would I put all the 5 weight parameters in first, and then the lengths parameter, then width parameters, etc, or would I put in all the parameters that would make it say a "LL", then all that would make it a "PKT", etc? I'm thinking about the scenario where one or more of the parameters would make it say a "PKT" but one or more of the other parameters would make it another ship type , ie "BOX"



CODE
Public Function UKShipMethod(ByVal dblParcelWt As Double ) as String

Select Case dblParcelWt
    Case 0.01 To 0.748
        UKShipMethod = "LL"
    Case 0.75 To 0.2
        UKShipMethod ="RM2"



Thank you for your continued support

Kindest regards
Mike
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th November 2018 - 04:42 PM