Remove part of address

[Karen]

I would like to remove part of addresses using the update query.

A sample address looks like this: 12 GOOSE LANE, PO BOX 70. I would like to
remove the comma and anything after where the address looks like 12 GOOSE
LANE. There may not always be a comma preceding the PO Box.

 

[KARL DEWEY]

There may not always be a comma preceding the PO Box.
Will there alway be a 'PO Box'? You gotta have something to select off of
to make it work.
For commas --
LEFT([address], InStr([address], ",")-1)

For 'PO Box' after doing the above commas query --
LEFT([address], InStr([address], "PO Box")-2)

 

[John W. Vinson]

I would like to remove part of addresses using the update query.

A sample address looks like this: 12 GOOSE LANE, PO BOX 70. I would like to
remove the comma and anything after where the address looks like 12 GOOSE
LANE. There may not always be a comma preceding the PO Box.

If there's not a comma, how can you tell what to trim? Will there ALWAYS be a
"PO BOX" - or might the address contain "POB 3123" or "Box 81" or "Drawer M",
with or without a comma? Might there be two commas?

If it's JUST a comma, try an Update query. BACK UP YOUR DATABASE FIRST! This
is a destructive one-way operation:

UPDATE yourtable
SET [Address] = Left([Address], InStr([Address], ",") - 1)
WHERE [Address] LIKE "*,*"

Then manually search for missed examples with a criterion

LIKE "* PO *" OR LIKE "*BOX*"

There may be false drops or missed records of course...