Trimming data

  • Thread starter Thread starter Debbie
  • Start date Start date
D

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
 
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)
 
Your suggestion almost works. I get the following error
message: "Data type mismatch in criteria expression."
Any other suggestions for me?
 
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.
 
Back
Top