Parse field in Query

[dataH]

Access 2007

I would like to break-up a text field in a query where leading numbers
(sometimes) will distort my search criteria.

Example: I want to sort this field by street name

150 Main Street
Spruce Pine Drive
200 Oak Blvd
400B Morgan Lane
Broad Street

Can i some how eliminate the leading numbers in a new field so i can sort by
street name only, but where their are no numbers leave alone.

Tough one?

Best regards,

dataH

 

[Piet Linden]

Access 2007

I would like to break-up a text field in a query where leading numbers
(sometimes) will distort my search criteria.

Example: I want to sort this field by street name

150 Main Street
Spruce Pine Drive
200 Oak Blvd
400B Morgan Lane
Broad Street

Can i some how eliminate the leading numbers in a new field so i can sortby
street name only, but where their are no numbers leave alone.

Tough one?

Best regards,

dataH

SELECT txtAddresses.RemoveNumberFromAddress, IIf(IsNumeric(Left$
([RemoveNumberFromAddress],1)),Left$([RemoveNumberfromAddress],InStr(1,
[RemoveNumberFromAddress]," ")-1)) AS NumericPart, IIf(IsNumeric(Left$
([RemoveNumberFromAddress],1)),Mid$([RemoveNumberFromAddress],InStr(1,
[RemoveNumberFromAddress]," ")+1),[RemoveNumberFromAddress]) AS Street
FROM txtAddresses;