Replace

  • Thread starter Thread starter JoHickey
  • Start date Start date
J

JoHickey

I need to replace everything past a specific character "~" with nothing. For
example:

West Elgin~West Elgin~West Elgin
Hamilton~Hamilton
Guelph~Guelph~Guelph~Guelph

I would like to have the query return only the information up to but not
including the first ~. The problem is that the number of characters is not
the same on all records, therefore using Left or Replace isn't working.

Any help would be appreciated. Thanks.
 
Create a calculated field in your query based on the field with the data. In
the example, x is the name of the field:

ShortStyle: left(x,instr(x,"~")-1)
 
I need to replace everything past a specific character "~" with nothing. For
example:

West Elgin~West Elgin~West Elgin
Hamilton~Hamilton
Guelph~Guelph~Guelph~Guelph

I would like to have the query return only the information up to but not
including the first ~. The problem is that the number of characters is not
the same on all records, therefore using Left or Replace isn't working.

Any help would be appreciated. Thanks.
Click to expand...

Left() works fine.
You just need to find the position of the first "~" using InStr(), and
subtract 1.

NewColumn:Left([FieldName],InStr([FieldName],"~")-1)
 
Back
Top