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.
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.