UtterAccess Discussion Forums > Replace Query Wildcard Question

Full Version: Replace Query Wildcard Question

UtterAccess Discussion Forums > Microsoft® Access > Access Queries

MFS3000

Dec 2 2011, 08:14 AM

Hi,
I'm sure this is easy and I'm just overlooking something but I'm trying the use an update query with a replace statement to remove part of the data in a field but am having no luck...

I have a column with this type of data:

Column Name = test
RGRR.N0000
EWES.W0014
YUY.P0022
UYUI.N0001

After running the query I'd like to data to just be:
RGRR
EWES
YUY
UYUI

In the update to field I have Replace([test],".*","") - thinking this would remove anything after the dot in the data but it doesn't seem to work...

Thanks for any pointers you can offer in where I'm gonig wrong....

Doug Steele

Dec 2 2011, 08:29 AM

Try

CODE

Left([test], InStr([test], ".") - 1)

If there's a chance that there might be entries without the period, try

CODE

IIf(InStr([test], ".") > 1, Left([test], InStr([test], ".") - 1), [test])

MFS3000

Dec 2 2011, 08:37 AM

Works a dream! Thanks for your help!

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.