SQL execute statement to Update field2 with subset of field1

[Eagle One]

2007

Is there an Access function to update field2 with a subset of the contents of field1?

Specifically, I know that I can use a Mid() function but how can I code an SQL statement to:

CurrentDb.Execute "UPDATE COA_EOY Set field2 = Mid([field1],1, ????? position of ":" ?????)

In other words, is there an available function to Find or Search the position number of ":" in the
contents of field1.

If not, is there an alternate approach?

TIA EagleOne

 

[Ken Snell \(MVP\)]

CurrentDb.Execute "UPDATE COA_EOY Set field2 = Mid([field1],1,
InStr([field1], "":"")) WHERE InStr([field1], "":"") > 0;"

 

[EagleOne]

Thanks Ken.

After I asked the question, I found InStr and InStrRev.

What is the best source (list) of Functions available to the coder attempting to "Execute" SQL
commands?

Be sure that I attempted to do that before asking the question. I easily found Ltrim, RTrim, Mid
etc. via "Access Functions" but I finally did a direct search on "InStr()" and found it.

There must be a better way to locate available (i,e, String or Other) functions without having to
know their names first?

 

[Ken Snell \(MVP\)]

I'm using ACCESS 2003 to create this list of directions for how to find the
list of functions in ACCESS Visual Basic Editor:

Open Visual Basic Editor (VBE).

Click on Help; select Microsoft Visual Basic Help.

Click on Microsoft Visual Basic Documentation item in the list. Sublist will
become visible.

Click on Visual Basic Language Reference item in the list. Sublist will
become visible.

Click on Functions item in the list. Sublist will become visible.

Sublist shows alphabetical categories for the list of functions.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Thanks Ken.

After I asked the question, I found InStr and InStrRev.

What is the best source (list) of Functions available to the coder
attempting to "Execute" SQL
commands?

Be sure that I attempted to do that before asking the question. I easily
found Ltrim, RTrim, Mid
etc. via "Access Functions" but I finally did a direct search on
"InStr()" and found it.

There must be a better way to locate available (i,e, String or Other)
functions without having to
know their names first?

CurrentDb.Execute "UPDATE COA_EOY Set field2 = Mid([field1],1,
InStr([field1], "":"")) WHERE InStr([field1], "":"") > 0;"

 

[Steve Schapel]

Eagle,

The easiest way to get this right is to make your Union Query first, with
the 3 existing tables. Save it. Then use this query as the basis of your
Make-Table Query.