Solved: Update Query not working

[Trevor]

I have an update query that should update a field values to the variable value, I have tested the variable by msgbox (string name) to varify that the value is a real vaule but my fields are only updated with "0" or "-1"
heres my query

DoCmd.RunSQL "UPDATE IndividualSettingsTbl SET " & _
"IndividualSettingsTbl.LabelBKColor = '" & stLabelBoxBC & "'And IndividualSettingsTbl.LabelForeColor = '" & stLabelBoxFC & "' " & _
"And IndividualSettingsTbl.LabelFontStyle = '" & stLabelFont & "' And IndividualSettingsTbl.TextComboBKColor = '" & stTextBoxBC & "' " & _
"And IndividualSettingsTbl.TextComboForeColor = '" & stTextBoxFC & "' And IndividualSettingsTbl.TextComboFontStyle = '" & stTextFont & "' " & _
"Where IndividualSettingsTbl.UserName = '" & AccountName & "';"

Thanks for helping

[Trevor]

Correction even thaugh I am allowing warning I get the warning that x # of rows are going to be updated but then nothing is updated

[ben.oates]

You should use a comma instead of the "And" operator. Try this:

DoCmd.RunSQL "UPDATE IndividualSettingsTbl SET " & _
"IndividualSettingsTbl.LabelBKColor = '" & stLabelBoxBC & "', IndividualSettingsTbl.LabelForeColor = '" & stLabelBoxFC & "' " & _
", IndividualSettingsTbl.LabelFontStyle = '" & stLabelFont & "' , IndividualSettingsTbl.TextComboBKColor = '" & stTextBoxBC & "' " & _
", IndividualSettingsTbl.TextComboForeColor = '" & stTextBoxFC & "' , IndividualSettingsTbl.TextComboFontStyle = '" & stTextFont & "' " & _
"Where IndividualSettingsTbl.UserName = '" & AccountName & "';"

[ben.oates]

Sorry... to explain... because you are using And the output of 0 and -1 is the result of a Boolean function that the SQL is trying to do. The comma is the correct separating operator for an Update query.

[Trevor]

Thanks ben, this may sound stupid, yesterday before posting I tried using , instead of and but I think I had either 1 to many or to few spaces between arguments, thanks again that works

[Trevor]

ahhh. queries can be so frustrating, I have a query that should update the date in the table to the current date
where the username in the table = the ID of the account loged in , and when I run it I don't even get warning "updating x records":

DoCmd.RunSQL "UPDATE IndividualSettingsTbl SET IndividualSettingsTbl.Date = '" & stDate & "' " & _
"Where IndividualSettingsTbl.UserName = '" & AccountName & "';"

stDate is declared as a date that is = Date , I tried useing Date(), Date instead of a variable think access just wanted to be picking but didn't work

[ben.oates]

I'm not 100% but I think dates in Access SQL are usually surrounded by a # rather than a ' character... but don't quote me on this because I very rarely use Access for databases I have to write SQL in! Also, do a break just to make absolutely sure the SQL is syntactically accurate and that there actually is an entry for UserName in the IndividualSettingsTbl table, because it won't ask to update any rows if there aren't any there to change.

[ben.oates]

Actually, I just checked and both a hash and an apostrophe work. Would you try putting IndividualSettingsTbl.Date in square brackets? It shouldn't be a problem because you've included the table name, but having the column name "Date" can sometimes cause syntax problems. I can't think of anything else.

[Trevor]

Thanks Ben, I just had to put braces [] around Date for the date coume, thanks again it worked

[ben.oates]

No problem. Glad it helped.