Trimming data

[Debbie]

I am trying to trim off the municipality from a list of
addresses in a table in MSAccess 97. The street names and
municipality are all in one field i.e. Golf Links Road,
Ancaster; Govenors Road, Ancaster etc. Does anyone have
any suggestions on how to do this?

Debbie

 

[John Spencer (MVP)]

IF and ONLY IF the field always has one comma and the comma is between the
street name and the municipality.

Field: Municipality: Mid(FieldName,Instr(1,FieldName,", ")+1)

 

[Debbie]

Your suggestion almost works. I get the following error
message: "Data type mismatch in criteria expression."
Any other suggestions for me?

 

[John Spencer (MVP)]

WHAT criteria expression?

If you are trying to limit the results to a certain municipality, you might use

Field: FieldName
Criteria: Like "*," & "Ancaster"

Calculate your Municipality in the other field.

Also, this whole thing could have a problem is there is NO value or NO comma

Try:

Field: Municipality:
IIF(FieldName & "" Like "*,*",
Mid(FieldName,Instr(1,FieldName,", ")+1),
Null)

All on one line.