Access equiv. of Excels =Search("_",A1)

When reducing UK Postcodes from unit postcode MK19_1PY to Postcode Sector
MK19_1 I used to use =Search("_",A1) to identify the position of the blank in
the text string.

In the MK19_1PY example, the result would be character number 5 in Cell B1.
I would then use =Left(A1,B1+1) to provide me with the shortened Post Code
sector. UK postcode blanks are not in a uniform position eg WN1R_2XN, so it
is necessary to identify position of blank.

How do I write the above to functions when using Access Expression builder?

D

InStr is the equivalent of Search.

You should be able to do it all in one statement:

Left([PostCode], InStr([PostCode], " ") + 1)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Duncan" wrote in message
...
When reducing UK Postcodes from unit postcode MK19_1PY to Postcode Sector
MK19_1 I used to use =Search("_",A1) to identify the position of the blank
in
the text string.

In the MK19_1PY example, the result would be character number 5 in Cell
B1.
I would then use =Left(A1,B1+1) to provide me with the shortened Post Code
sector. UK postcode blanks are not in a uniform position eg WN1R_2XN, so
it
is necessary to identify position of blank.

How do I write the above to functions when using Access Expression
builder?

D