Return warehouse for postcode

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'd like to put in a If..Then..Else statement on my form. On the
Postcode_After Update event I want Access to 'read' the first two letters of
the postcode and depending on what they are, return the name of the closest
warehouse in the next field.

So, in words, If the first two letters of the postcode are AB or B or BB or
BD or BL etc then the Warehouse field should read Kendal Else if the first
two letters of the postcode are AL or CB or CM or CO or CT etc then the
Warehouse field should read Rushden Else the Warehouse field should read
Bristol.

Any comments and help appreciated.
 
You say first 2 letters and then have a post code of B ?
You also say etc. so are there lots of options other than the ones you have
stated ?
If you are always going to check the first 2 letters of a postcode then
create a table with 2 fields. The first is the 2 letters and the second is
the warehouse.
In your PostCode_AfterUpdate event you can then put

Me.WareHouse = DLookUp("[WareHouse]","YourTable","[PostCode] = '" &
Left([PostCode],2) & "'")

I would also put a check to see that you have entered at least 2 characters
in your postcode and that it is a valid entry in your table.
 
The reason one of the postcodes is one letter is because MOST postcodes start
with two letters however some start with one letter? UK postcodes, eg, M for
Manchester, but NE for a postcode in London.

I might do that table thing though and see if that works well.

Thanks.

Dennis said:
You say first 2 letters and then have a post code of B ?
You also say etc. so are there lots of options other than the ones you have
stated ?
If you are always going to check the first 2 letters of a postcode then
create a table with 2 fields. The first is the 2 letters and the second is
the warehouse.
In your PostCode_AfterUpdate event you can then put

Me.WareHouse = DLookUp("[WareHouse]","YourTable","[PostCode] = '" &
Left([PostCode],2) & "'")

I would also put a check to see that you have entered at least 2 characters
in your postcode and that it is a valid entry in your table.


albycindy said:
I'd like to put in a If..Then..Else statement on my form. On the
Postcode_After Update event I want Access to 'read' the first two letters of
the postcode and depending on what they are, return the name of the closest
warehouse in the next field.

So, in words, If the first two letters of the postcode are AB or B or BB or
BD or BL etc then the Warehouse field should read Kendal Else if the first
two letters of the postcode are AL or CB or CM or CO or CT etc then the
Warehouse field should read Rushden Else the Warehouse field should read
Bristol.

Any comments and help appreciated.
Click to expand...
Click to expand...
 
Back
Top