Refering to table columns by name in SQL statement

M

Max Moor

Hi All,
I want to run an update query to concatinate one field in a table
onto another one, with a comma and space between them. This is the basic
SQL:

strSQL = "UPDATE tblMine SET tblMine.F1 = tblMine.F1 & ', ' & tblMine.F2;"

This works fine. The problem is that I don't know the names of the
columns (F1 and F2 in the example) until runtime. I tried the SQL:


strF1 = "F1"
strF2 = "F2"

strSQL = "UPDATE tblMine SET tblMine('" & strF1 & "') = tblMine('" & strF1
& "') & ', ' & tblMine('" & strF2 & "');"


but I get a syntax error. The error is in the attempted use of syntax
like:

tblMine("F1")

in the SQL. Obviously, I don't know what to do to make it better, though.
Can anyone set me back on the straight and narrow?

Thanks, Max
 
V

Van T. Dinh

You are constructing the SQL String so try to use
concatenation whose is the SQL String that works.

Try:

strSQL = "UPDATE tblMine SET tblMine." & strF1 & _
" = tblMine." & strF1 & " & "", """ & _
" tblMine." & strF2
Debug.Print strSQL

The Debug statement will print the constructed SQL String
in the Debug/Immediate window so that you can check the
SQL String.

Since your SQL only involves 1 Table, you can omit the
Table qualifier like:

strSQL = "UPDATE tblMine SET " & strF1 & " = " & _
strF1 & " & "", "" & " & strF2
Debug.Print strSQL

HTH
Van T. Dinh
MVP (Access)
 
Top