Remove redundant text

[Rich Sullivan]

How would I remove in a query the last or right most characters or a constant
value like:

New York, NY
Rochester, NY
Buffalo, NY

And I want to remove ", NY"

Thanks in advance for the help

 

[ghetto_banjo]

The below function will return everything except the last 4 characters
of a string.


Left([FieldName], Len([FieldName]) - 4)

 

[Daryl S]

Rich -

Back up your table or database before updating your data.

You will use an Update query. You will need to know what you are removing.
If it is ALWAYS the last 4 characters of the field, then your update would
look like this:

UPDATE <tablename>
SET <fieldname> = LEFT(<fieldname>,len(<fieldname>)-4);

Substitute your table and fieldnames.

If your data is more complex, please provide more details so we can help.

 

[KARL DEWEY]

He said remove in a query, not the table.
It might be possible in a query but very easy in a report IF your data was
laid out correctly. You should have city in one field and state in another.
Add a field for state and do the steps Daryl said but first update the state
field using the right 2 characters. I would run a totals query on the
updated state field to verify that the updates were correct abbreviations.
Then run an update on the city field.

In a report you set the Hide Duplicates property of the state field to Yes
and it will show the first one only.

 

[Rich Sullivan]

Thanks with your help I was able to write in a select query which will help
me toward my ultimate goal.

 

[Rich Sullivan]

This was the actual syntax

Left([dbo_NCP_BackUp1]![UNIT_ID],Len([dbo_NCP_BackUp1]![UNIT_ID])-12) AS
UNIT_ID

This removed the last 12 letters

 

[John W. Vinson]

This was the actual syntax

Left([dbo_NCP_BackUp1]![UNIT_ID],Len([dbo_NCP_BackUp1]![UNIT_ID])-12) AS
UNIT_ID

This removed the last 12 letters

Well... yes. That's what you asked it to do, and it does not match
GhettoBanjo's advice.