Parse field in Query

  • Thread starter Thread starter dataH
  • Start date Start date
D

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
 
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
Click to expand...

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;
 
Back
Top