Set column name with variable

[jgelpi16]

I am running some update SQL and I need to have the field that is being updated contingent on my variable. Is there a way to do such a thing? Thanks in advance for your help.

dim vDivisionNum
vDivisionNum = Column1

mySQL = "UPDATE tblDivBreakdown SET [vDivisionNum]=52 WHERE [FirstName]='Martin'  "AND [LastName]='Green';"
CurrentDb.Execute mySQL, dbFailOnError

[Rixxe]

DECLARE @sql nvarchar(1000)
SET @sql = 'SELECT ' + @columnName + ' FROM Table1'
EXEC(@sql)

DECLARE @SQL nvarchar(1000)
SELECT @SQL = 'UPDATE Table SET ' + @field + ' = ''0'' WHERE AnotherField = "" 
EXEC(@SQL)

Is that the kind of thing you want to do?

If so, i'm not sure thats always a good idea.

[jgelpi16]

What I'm doing is kind of conveluted and I'll be the first to admit. I believe it's a bit too complicated to describe in text. Essentially I am obtaining data from one source and I need to append it to the table. The field names are in a standard format and really the only way to get the source in there is to define the field name in my "for...each" loop and then dump the value into the specific field.

I'm not familiar with SQL in VBA so I'm not sure I understand what you gave me until I play around with it. I want to take the value of the variable vDivisionName in my example and use that for the Column/Field name in my UPDATE SQL.

[Rixxe]

What I'm doing is kind of conveluted and I'll be the first to admit. I believe it's a bit too complicated to describe in text. Essentially I am obtaining data from one source and I need to append it to the table. The field names are in a standard format and really the only way to get the source in there is to define the field name in my "for...each" loop and then dump the value into the specific field.

I'm not familiar with SQL in VBA so I'm not sure I understand what you gave me until I play around with it. I want to take the value of the variable vDivisionName in my example and use that for the Column/Field name in my UPDATE SQL.

To use a value of a field and declare it as a field name is possible, However my lack of knowledge with VBA means i'm unable to help you with that side of things.

[jgelpi16]

Thanks for your help. I have the below SQL that does not work. I have a table with the field with the same exact name. I have the table values defaulted to 0. I don't receive any errors when I step through the code. Any ideas as to why it isn't working?

    mySQL = "UPDATE tblDivBreakdown SET [HSLC00_Div5]=" & vFieldVal & " WHERE [BegWk]=#" & vBegWk & "#"
    CurrentDb.Execute mySQL, dbFailOnError

[SteveF]

Thanks for your help. I have the below SQL that does not work. I have a table with the field with the same exact name. I have the table values defaulted to 0. I don't receive any errors when I step through the code. Any ideas as to why it isn't working?

    mySQL = "UPDATE tblDivBreakdown SET [HSLC00_Div5]=" & vFieldVal & " WHERE [BegWk]=#" & vBegWk & "#"
    CurrentDb.Execute mySQL, dbFailOnError

What's the data type of [HSLC00_Div5]? If it's a text/string data type, you need to enclose it in quotes:

mySQL = "UPDATE tblDivBreakdown SET [HSLC00_Div5]=" & chr(34) & vFieldVal & chr(34) & " WHERE [BegWk]=#" & vBegWk & "#;"

Also, don't forget the semicolon at the end -- that's how SQL knows that a multi-line statement has been completed and can be executed.

Steve