Refering to table columns by name in SQL statement

  • Thread starter Thread starter Max Moor
  • Start date Start date
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
 
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)
 
Shoot. Thanks, Van. I got it so deep in my head that I needed to use
the parentheses, that I completely spaced that the SQL was just a string
I could build that way in the first place. Maybe I need a break. :-)

Thanks Again,

Max
 
Back
Top