Find Values With Letter Then Number

[hullstorage]

Hi all i have an update query that i am using which gets the results from text boxes on a form

everything was working fine but i have now hit a slight problem as this query works from postcodes

so if i want to update postcode beginning with the letter L then the number i.e. L1 to L20 i have been using a wildcard

on my form i have been using L* but this method also changes any postcode like LL or LT etc but i only want it to change all postcode that start with L and then have a number next to it

so its like this
ALL POSTCODE IN LIVERPOOL ARE SAY L THEN A SINGLE DIGIT AND THEN CHANGE L WITH A DOUBLE DIGIT


L1 TO POSTCODE L15 WILL UPDATE THE COST FIELD TO £25.00
LL1 TO POSTODE LL22 WILL UPDATE THE COST FIELD TO £28.00
LN1 TO POSTCODE LN33 WILL UPDATE THE COST FIELD TO £27.50

 

[Brianwarnock]

try
L#

Brian

 

[hullstorage]

ok thanks will try

 

[hullstorage]

did not work any other ideas

 

[Brianwarnock]

You did code Like "L#*" it will return eg L1 3qq or L11 3qq but not LL1 etc

Brian

 

[hullstorage]

You did code Like "L#*" it will return eg L1 3qq or L11 3qq but not LL1 etc

Brian

thanks for that worked a treat
could you also help me with this
all postcodes beginning with PO1 would equal a charge of 26.50
but all beginning with PO11 would equal a charge of 32.80
how would i get around this

thanks
simon

 

[gemma-the-husky]

if you have many variants i would consider changing your system - have another table that stored all the legitimate "left" postcodes - there are only a few hundred of these I think

PO1
Po2
PO3
..
...
PO11 etc

then you could store the appropriate rate in this table.

Then given a real post code eg PO1 2BZ, you could join the left part of the full post code to the postcode lookup table, and get the appropriate rate in that way.

This is much cleaner than having to deal with all these special cases.

 

[Insane_ai]

For the P01 and PO11, use a nested IIF statement.
IIF([PostCode] = "PO1",26.50,(IIF([PostCode] = "PO11",32.80,))) the empty "value If False" section in the second IIF statement will return nothing (blank field) in your query.

 

[Insane_ai]

You may want to follow Gemma-the-Husky's suggestion. If you have a lot of post codes to deal with a second table with the codes and rates is the best way to go.