If then grouping variables help (1 Viewer)

RobBhat · Sep 25, 2016

Hi all....I am a novice on access and have a very basic knowledge and I need help with VBA coding. I have made an Access 2003 DB. I have a form with Field1, Field2, Field3, Field4. Field1, Field2, Field3 are address fields, but people have used all 3 fields to insert post codes.

I need a delivery cost to be inserted in Field4 depending on the prefix of the postcode filled in one of the fields in Fields 1,2,3.

The delivery cost is the same for a lot of the postcode prefixes

I will use postcode prefixes AB10-16; AB17-21, BB, BL, DN1-9, DN10, DN11-12, DN13 as an illustration.

a) To AB10-16, BB, BL, DN10, DN13– Delivery cost is £50

b) To AB17-21, DN1-9, DN11-12 - Delivery cost is £60

I have used the following code which works, but is inefficient:

If (Me.Field1.value Like"AB10*" Or Me.Field2.value Like"AB10*" Or Me.Field3.value Like"AB10*") Then Me.Field4.Value = "50"

If (Me.Field1.value Like"AB17*" Or Me.Field2.value Like"AB17*" Or Me.Field3.value Like"AB17*") Then Me.Field4.Value = "60"

But, I have to do this about 250 times for individual prefixes. I would like some help to group them together by giving a group with the same delivery cost a name and using it in the code.

Eg. DeliveryGroup1 = AB10, AB11, AB12, AB13, AB14, AB15, AB16, BB, BL, DN10, DN13
DeliveryGroup2 = AB17, AB18, AB19, AB20, AB21, DN1, DN2, DN3, DN4, DN5, DN6, DN7, DN8, DN9, DN11, DN12

Then use this in the code as below

If (Me.Field1.value Or Me.Field2.value Or Me.Field3.value Like"DeliveryGroup1*") Then Me.Field4.Value = "50"
If (Me.Field1.value Or Me.Field2.value Or Me.Field3.value Like"DeliveryGroup2*") Then Me.Field4.Value = "60"

Can someone please help me with this?

Thank you...Rob

sneuberg · Sep 25, 2016

Edit: The data in the attached database is not normalized correctly. There should be two tables. One relating prefixes to groups and another relating groups to cost. I try to get to a fixed solution later today.

One way you might consider is to define the costs in term of the prefixes in a table. In the attached database I created such a table name PostalCost. With this table you could lookup the cost of postage after removing the prefix from the postal code. You could do this with a function like:

Code:

'Returns the part of a string before a dash or the PostCode if no dash
Private Function GetPrefix(PostalCode As String) As Variant

Dim DashPos As Long
If Len(PostalCode) = 0 Then
    GetPrefix = Null
    Exit Function
End If
DashPos = InStr(1, PostalCode, "-")
If DashPos = 0 Then
    GetPrefix = PostalCode
Else
    GetPrefix = Left(PostalCode, DashPos - 1)

End If

End Function

Then you could look up the cost with DLookup like:

Code:

PostalCost = DLookup("[Cost]", "[PostalCost]", "[Prefix] =  '" & GetPrefix(Me.PostalCode) & "'")

This is demonstrated in the attached database.

sneuberg · Sep 25, 2016

I've attached a database that has two tables for maintaining the groups and costs. This works about the same as in my previous post. The main difference is that the DLookup uses a query of the two tables instead of a table. I included an additional form MaintainGroups which is one of those ugly Access created forms but it will give you the idea.

RobBhat · Sep 25, 2016

Thank you so much for your effort..I will study these and give it a go at implementing them....You are of great help...Rob

RobBhat · Sep 26, 2016

Hi again...This is very promising, but there is a snag which I would urge you to get me through. There are 3 more variables that need to be selected leading to a different price.

1) The size of the pallet eg 1,2,3 etc - prices are not multiples of 1 and a linier equation cant be applied
2) Whether the delivery is 'next day' or 'economy'
3) The delivery company with different charges for each post code - there are 3

How can this be incorporated?

Thank you...Rob

sneuberg · Sep 26, 2016

This can be incorporated but if there's no way to simplify this it could be a data entry night nightmare i.e., the number of entries being about 250 (number of prefixes) x number of pallet sizes x 2 (day or night) x 3 (delivery company). So I have a few questions.

Do the companies use the same postal codes?

Is the grouping of postal prefixes the same for each company?

Does the grouping apply for whether the delivery is next day or economy?

Does the group apply to the pallet size?

Can you get these rates on line? If not immediately then at least for updating your database.

Is there any other relationship between the variables?

RobBhat · Sep 26, 2016

Hi again (i feel bad that I dont know your name..>I am sorry)

I wish I could attach a screen print of the delivery note form where all these things apply. is there a way?

1) All companies have different post codes but the prefixes are common amongst them (Thats why I have to use the 'Like (...*) function
2) 98% of companies have a unique prefix eg AB10, but 2% have different delivery addresses and will have different prefixes
3) & 4) On the delivery note form, after a customer is selected and a delivery address is selected for that customer, the post code will populate a field on the form. The user has to select the transport company (there are 3), the pallet size (0-4), and the service (economy or next day). For a particular group of post codes, (say AB10, AB11 and BL), the delivery charge will depend on the 3 variables - transport company, no of pallets, next day or economy. Eg for a particular group of post code prefixes(eg AB10, AB11, BL)

Delivery company Pallet Service Delivery charge
Freight route 1 Next day 65
Freight route 2 Next day 110
Freight route 1 Economy 55
Freight route 2 Economy 102
Add Express 1 Next day 60
Add Express 2 Next day 108
Add Express 1 Economy 52
Add Express 2 Economy 105

5) the rates will have to be inputted by me when an update is required. luckily this is not required very often

6) There are no other relationships besides the above amongst the variables

I hope I have explained myself clearly.

Thanks again

Rob

sneuberg · Sep 26, 2016

RobBhat said:
I wish I could attach a screen print of the delivery note form where all these things apply. is there a way?

You can zip the screen print and then you can upload it. After you have ten posts you won't have to zip images.

Steve

RobBhat · Sep 26, 2016

Hi steve

I have reduced the fields to what is necessary

I have my complicated If Then VBA in the After Update Event for the following fields:
Customer
Transport company
Service

The User does the following:

1 Chooses a customer
2 Then he/she has delivery address displayed to choose from in the subform 'Delivery addresses to choose from'; He/She double clicks on the one he/ she wants. This populates the 'Delivery address Selected' Fields
3 He/She chooses a transport company (Dropdown list of 3)
4 Selects the number of pallats (Increments of 0.5 upto 4 from a dropdown list)
5 This should populate the 'estimated delivery cost' field that we are working towards
6 He/She fills in the products in the subform at the bottom

I am really grateful for your time

Regards...Rob

sneuberg · Sep 26, 2016

Sorry but stuff has come up and I can't help you with this today. I'll try to get back to this early tomorrow.

RobBhat · Sep 26, 2016

Hi Steve...Thats absolutely fine...

RobBhat · Sep 27, 2016

Hi Steve...About your question below, it is correct...Different delivery companies have different prices for the same group of prefixes for post code. The Groups are however, similar. Thats why I was hoping to have a way to refer to a group of post codes to apply into an If then VBA...I also know that this means that I have to update the VBA with new prices as and when required.

Best wishes

Rob

From what you have posted it appears that the groups vary by delivery company, for example, Group 1 rate for Freight route might be these prefixes

AB10, AB11, AB12, AB13, AB14, AB15, AB16, BB, BL, DN10, DN13

while for Add Express it might be these prefixes

AB10, AB11, AB12, AB13, AB14, AB15, AB16

BB, BL, DN10, DN13 could be in some other group for Add Express

Is my understanding correct?

sneuberg · Sep 27, 2016

Yeah I deleted that post but I guess you saw it in your email. If the groups are only similar we will have to figure out the exceptions but let's get something working that assumes they are the same. I should have something for you soon.

RobBhat · Sep 27, 2016

Thank you again Steve...Rob

RobBhat · Sep 27, 2016

Hi Steve...Just to clarify why I want to Group certain post codes..>its because certain groups have the same delivery cost. So, Company A will charge the same to deliver to the group of post codes of a certain group. For the same Group , Company B will charge an amount different to company A but its charges will be the same for all post codes within that group.

I hope I have managed to explain properly

Regards

Rob

sneuberg · Sep 27, 2016

I've attached a database that needs work but should be a starting for talking about this. I'll get an explanation to you of how it works later. For now please look over the table definitions especially the DeliveryCharge and PostalGroups table to see in they seem right. You could test the form and see if it works the way you think it should. Note that the button on the form can be replaced by an event. I just didn't want to spend time on that right now.

RobBhat · Sep 27, 2016

Hi Steve...This looks very promising!!. Thank you. I cant see the relationships on the tables for some reason....Something I need to do for viewing it?

Best wishes...Rob

RobBhat · Sep 27, 2016

Hi again Steve...Could it be that relationships are not needed for this?

All the best

Rob

sneuberg · Sep 27, 2016

No. Relationships should be add. I just put something together quickly to explore what's needed in the data structure. I'll get back to this as soon as I can.

RobBhat · Sep 27, 2016

Hi Steve...In your query, can you explain the following below? I can understand the rest:

((DeliveryCharge.Group)=GetGroup([Forms]![frmDelivery]![CompanyName])) AND

(Where ‘Group’ in the table Delivery charge = ? I don’t understand the GetGroup syntax. Can you pls explain?)

Thanks...Rob

If then grouping variables help (1 Viewer)

Registered User.

AWF VIP

Attachments

AWF VIP

Attachments

Registered User.

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

Attachments

AWF VIP

Registered User.

Registered User.

AWF VIP

Registered User.

Registered User.

AWF VIP

Attachments

Registered User.

Registered User.

AWF VIP

Registered User.

Similar threads

Users who are viewing this thread