Expression Builder and Update Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

THINKINGWAY - 09 Nov 2007 17:58 GMT

I am trying to create a query that looks at a single table with a field call
region. The region field begins with the letters SD, format is "SD REGION -
CITY". I would like to remove the SD and CITY from the field leaving no
spaces but only the REGION. I tried to use QUERY Builder with Expression
Builder....here's my valiant stab at it.

UPDATE [X-WMS] SET [X-WMS].REGION_NAME = Replace("SD","",1,1,2);

Reply to this Message

KARL DEWEY - 09 Nov 2007 19:49 GMT

Try this if the field always starts with 'SD ' and only has ' - ' before the
city.

UPDATE [X-WMS] SET [X-WMS].REGION_NAME = Left(Replace([X-WMS].REGION_NAME],
"SD ",""), InStr(Replace([X-WMS].REGION_NAME], "SD ",""), " - ") -3) ;

Signature

KARL DEWEY
Build a little - Test a little

> I am trying to create a query that looks at a single table with a field call
> region. The region field begins with the letters SD, format is "SD REGION -
[quoted text clipped - 3 lines]
>
> UPDATE [X-WMS] SET [X-WMS].REGION_NAME = Replace("SD","",1,1,2);

Reply to this Message