SQL Help with Access VBA

[Megadeth]

This is a snippet of Code from my Access Project. I am trying to get me Variable to work for strFieldName.

strFieldName = "[Last Name]"
strLastName = Me![Last Name]
strInput = InputBox("Which Lot will you move data to?", "Lot Transfer", , 5000, 5000)

strSQL = "UPDATE Lot SET '" & strFieldName & "' = '" & strLastName & "' WHERE [Lot Number]= '" & strInput & "';"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True


Before I had everythign as is except I decided to change strSQL from


strSQL = "UPDATE Lot SET [Last Name]

to


strSQL = "UPDATE Lot SET '" & strFieldName & "'


Everything was working perfectly before I did that.
The whole reason I wanted to make that part of the Statement a variable was because I have several fields that I would like to move all at once to another record and I was just going to make a simple loop that will change the variable string based on the count number.

I have tried the following:
strFieldName="[Last Name]"
Which gives me a Invalid use of Brackets
strFieldName=[Last Name]
Which makes teh statement thing the field name is actually the data in the field.
strFieldName="Last Name"
Which prompts me to enter parameter for 'Last Name'

If I can't figure this out I have an alternate method but it will look messy and take ffar more code.

Thanks for any help anyone can provide.

 

[torpid]

This:

strSQL = "UPDATE Lot SET '" & strFieldName & "' = '" & strLastName & "' WHERE [Lot Number]= '" & strInput & "';"

Should be this:

strSQL = "UPDATE Lot SET " & strFieldName & " = '" & strLastName & "' WHERE [Lot Number]= '" & strInput & "';"

Note the lack of single quotes around the fieldname.

 

[Megadeth]

Wow... My first thought when I read this reply was "You have got to be kidding me"... I tried just about everything else. Anyway, that works perfectly! Thank you so much.
What I don't understand is why the other variables require a single quote around them but the first one there does not.

 

[Valrandir]

Like torpid said, do not enclose field names with the ' character. This is reserved to enclose strings values, not tables or field names.

Good:
Update TableName Set FieldName = 'Something'

Bad:
Update TableName Set 'FieldName' = 'Something'