Update query to replace everything after comma

[richb]

I'm trying to remove everything after the first comma in a column of
addresses. It seems like it should be simple but I can't get it to work. I
have tried:

UPDATE tblCompetitor2 SET tblCompetitor2.OUTLET = Replace([OUTLET],",*","")

Is it something to do with the * wildcard?

Thanks

 

[KARL DEWEY]

In your 'Replace' statement the asterisk is just a puncuation mark and not a
wildcard. To be a wildcard it must be part of the 'Like' function.
BACKUP DATABASE BACKUP DATABASE
Try this --
UPDATE tblCompetitor2 SET tblCompetitor2.OUTLET =
LEFT([OUTLET],InStr([OUTLET],",")-1)

 

[John Spencer]

To prevent a problem you should test for the presence of the comma in the
field. Otherwise you are going to generate an error for any field that does
not have a comma in it. The left statement would become LEFT(Outlet,-1) which
generates an error since Left cannot handle negative numbers. OR if Outlet
is null, you will end up with an Invalid use of Null error.

UPDATE tblCompetitor2
SET tblCompetitor2.OUTLET = LEFT([OUTLET],InStr([OUTLET],",")-1)
WHERE Outlet Like "*,*"

An alternative would be to add a comma to the end of Outlet in the Instr.
That will always return a number
UPDATE tblCompetitor2
SET tblCompetitor2.OUTLET = LEFT([OUTLET],InStr([OUTLET] & ",",",")-1)


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County