If then statement help (1 Viewer)

RobBhat

Registered User.
Local time
Today, 02:19
Joined
Sep 24, 2016
Messages
73
Hi all....I have an Access 2003DB. I have a form with Field1, Field2, Field3, Field4, Field5, Field6. Field1, Field2, Field3 are address fields, but people have used all 3 fields to insert post codes. Field4 and Field5 are comobo boxes with the following selection options:

Field4 - 0.5 and 1
Field5 - A and B

I want prices to be inserted into Field6 according to the prefix of the postcodes chosen in Fields 1,2,3. I will use postcode prefixes AB10-16; AB17-21, BB, BL, DN1-9, DN10, DN11-12, DN13.

Delivery costs to be inserted in Field 6 are as follows

a) To AB10-16, BB, BL, DN10, DN13–

£50 when Field4 is 0.5 and Field5 is A
£60 when Field4 is 1 and Field5 is A
£55 when Field4 is 0.5 and Field5 is B
£65 when Field4 is 1 and Field5 is B

b) To AB17-21, DN1-9, DN11-12

£70 when Field4 is 0.5 and Field5 is A
£80 when Field4 is 1 and Field5 is A
£76 when Field4 is 0.5 and Field5 is B
£86 when Field4 is 1 and Field5 is B

There are over 250 post code prefixes and my method below is most inefficient as I have to split each group into individual prefixes (eg AB10-16 into AB10, AB11, AB12 etc):

Private Sub DeliveryCharge()

If (Me.Field1.value Like"AB10*" Or Me.Field2.value Like"AB10*" Or Me.Field3.value Like"AB10*") And Me.Field4.Value = "0.5" And Me.Field5.Value = "A" Then Me.Field6.Value = "50"

If (Me.Field1.value Like"AB10*" Or Me.Field2.value Like"AB10*" Or Me.Field3.value Like"AB10*") And Me.Field4.Value = "1" And Me.Field5.Value = "A" Then Me.Field6.Value = "60"

If (Me.Field1.value Like"AB10*" Or Me.Field2.value Like"AB10*" Or Me.Field3.value Like"AB10*") And Me.Field4.Value = "0.5" And Me.Field5.Value = "B" Then Me.Field6.Value = "55"

If (Me.Field1.value Like"AB10*" Or Me.Field2.value Like"AB10*" Or Me.Field3.value Like"AB10*") And Me.Field4.Value = "1" And Me.Field5.Value = "B" Then Me.Field6.Value = "65"

End if

I have to repeat this about a 1000 times to get a value for all the conditions for each prefix.

Can you please help me to find a code for this which is more efficient?

Thanks, Rob

I want it to do the following.
 

stopher

AWF VIP
Local time
Today, 09:19
Joined
Feb 1, 2006
Messages
2,396
Create a table with all your variants then either do a lookup or create a join query.
 

RobBhat

Registered User.
Local time
Today, 02:19
Joined
Sep 24, 2016
Messages
73
I am a novice at Access. But, I was thinking whether it be possible to group all the post codes with the same charges by giving it a name and then refer to that group in the If then statement?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:19
Joined
Aug 30, 2003
Messages
36,118
You could have a table of post codes and corresponding groups. I heartily agree with stopher's recommendation. You have more flexibility that way. The 50 rate being increased to 51 should not require the developer to modify anything, nor should a new post code, etc.

FYI, I moved your thread to a more appropriate forum.
 

RobBhat

Registered User.
Local time
Today, 02:19
Joined
Sep 24, 2016
Messages
73
Thank you Paul...I will work on the recommendation and hopefully achieve my goal ;)
 

Users who are viewing this thread

Top Bottom