Extracting part of a string for each record in a query result

[weezles]

hiya

I'm trying to extract the first part of a postcode (before a space) for each record in a query result (alternatively in a table and create query later on these results).

i have the following code which works for a single string but how do I do it for each record?

Dim strPostcode as string
Dim intPosition as integer
Dim strFirstPart as string

strPostcode = "EH15 1JJ"
intPostition = InStr(strPostcode, " ")

strFirstPart = Left$(strPostcode, intPosition)

Thanks

Lou

 

[KenReay]

Hi

Make a query

in the query grid

strFirstPart: Left$(strPostcode, InStr(strPostcode, " "))


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[weezles]

thats great thanks.

is there any way i can handle nulls?

Lou

 

[KenReay]

Hi

Depends what you want to do but maybe:

strFirstPart: Left$((Nz(strPostcode,"- "), InStr(Nz(strPostCode,"- "), " "))

see Nz() in help


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PHV]

If you want to preserve the Null:
strFirstPart: IIf(IsNull(strPostcode), Null, Left(strPostcode, InStr(Nz(strPostcode," "), " ")-1))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244