I'm having a bit of a dizzy day, I need a query to search for specific post codes, BUT we have two types of post code in our database, ones that begin with two letters and then one or two numbers, and ones that begin with one letter, then numbers. I need to extract post codes which begin with a one letter, where the following digit is numerical, and ignore codes that begin with two letters.

I'm sure the solution is very simple but I just can't seem to figure it out. Thanks in advance for any help you can give.

In a query create a calculated field like this

PCPart:Mid([Postcode],2,1)

Then in the criteria line of the same field type


<="9"

I think this works ok

Col


[This message has been edited by ColinEssex (edited 05-21-2002).]

Col

That worked a treat, you are a GOD!

Thanks alot!

Ash

Note that I have changed the criteria since my first response ! otherwise you would have missed postcodes with 0 or 9 as the second number


Col

Try using

IsNumeric(Mid([PostCodeField],2,1)= True

in the criteria of the postcode field in your query. (Just a guess though)

Col got there first but I'd be interested to know if this works.......

[This message has been edited by Fizzio (edited 05-21-2002).]

Col
You were right about changing the criteria it added quit a few back in to the list. Thanks again

Fizzio
I had a quick go with the other method and it didn't seem to work, but the theory looks sound, I might not have entered it correctly. Cols method also works well with the existing criteria in the query, so I just left it at that. Thanks anyway

Ash

Another way is in the criteria for postcodes type

Like "?#*"

? is the wildcard for alphabetic characters
# is the wildcard for numerical characters

HTH