Dispay first 2 letter of post code

[Simon]

i want a query that just dispays the first letters of the post code
and no number

eg. BS5 9JG i just want it to dispaly BS
eg B5 9GR i just wnat to dispay B

I have the following qery but it only slows the first 2 Characters
like B5 but i olny want it to show B

PostCode: Left([InvoicePostCode],2)


How do u make it only display letters

Thanks

 

[Guest]

The only way I can see it working is if you pass the post code to a function
which you have written which will check each character to see if it is a
number with isnumeric() then build the string you want to display and return
the result back to the query.

If you are only checking the first 2 characters, you may be able to build
the string with iif() within the query, but it may be too long
James

 

[Guest]

Try this:

PostCode: IIf(IsNumeric(Mid([InvoicePostCode], 2, 1)),
Left([InvoicePostCode], 1), Left([InvoicePostCode], 2))

Watch for line wraping.

This should return exactly what your are looking for.