VBA not recognizing specific record to update

[barbarao]

Hi. ACCESS ADP with SQL Server 2008. Have code that determines what a new overall score should be. The result shows in a message box with a Yes/No to update. When I select yes, getting a message about column name being wrong. it thinks rs!IDNumber is the column name when in fact it is the ID field for the record I want to update. Any help would be appreciated. Thanks.

tmpSQL = "Update PracticeTable " _
"Set PracticeTable.SortOverall=" & new_sort _
& " where PracticeTable.[IDNumber]=" & rs!IDNumber

[zmbd]

Insert

debug.print tmpSQL
Stop

on the line just below tmpSQL=...when the code hits the stop
<ctrl><g>
Check that your string is resolving correctly.

[barbarao]

Thanks but it was a problem of missing quotes around the rs!OPNumber

[Rabbit]

Do you understand the reason why that would cause an error?

[barbarao]

Not really other than it must be required.

[Rabbit]

The reason is because your IDNumber field is a string.
12345 is a number.
'12345' is a string.
12345 <> '12345' They are not the same even though they may look the same. They are stored differently in the database.

To tell SQL that something is a string, you surround the value in quotes.

[NeoPa]

A couple of links you may find helpful Barbara :
Quotes (') and Double-Quotes (") - Where and When to use them.
Before Posting (VBA or SQL) Code.