Update query problem

[Guest]

I am having a problem with my update query.
Reading.value is a combo box on the same form as the button that executes
this query. It executes an insert query first which works fine, and then an
update query which also works fine. The update query below then returns
"syntax error (missing operator) in query expression 'Entry 1'."
'Entry 1' is the selected value of Me.Reading.

Am I missing something obvious??

DoCmd.RunSQL "UPDATE [Entrants_Temp_Split] SET [Level] = " &
Me.Reading.Value & " WHERE (([Entrants_Temp_Split].[Person Code2] = " &
Me.dummy_person_code.Value & ") AND ([Entrants_Temp_Split].[Type] =
""Reading""));"

 

[Nikos Yannacopoulos]

Boots,

Apparently the Level field is type Text, so you need to "enclose" the
combo box reference in quotes in your expression, like:

DoCmd.RunSQL "UPDATE [Entrants_Temp_Split] SET [Level] = '" &
Me.Reading.Value & "' WHERE (([Entrants_Temp_Split].[Person Code2] = " &
Me.dummy_person_code.Value & ") AND ([Entrants_Temp_Split].[Type] =
""Reading""));"

In case the ]Person Code2] field is also Text, you need to do the same:

DoCmd.RunSQL "UPDATE [Entrants_Temp_Split] SET [Level] = '" &
Me.Reading.Value & "' WHERE (([Entrants_Temp_Split].[Person Code2] = '"
& Me.dummy_person_code.Value & "') AND ([Entrants_Temp_Split].[Type] =
""Reading""));"

Note: Single quotes work just fine as text delimiters in VBA, and
improve readability vs. double double quotes (no pun intended!).

HTH,
Nikos

 

[Guest]

Superb. Can't believe I didn't work that out. Many thanks

Boots,

Apparently the Level field is type Text, so you need to "enclose" the
combo box reference in quotes in your expression, like:

DoCmd.RunSQL "UPDATE [Entrants_Temp_Split] SET [Level] = '" &
Me.Reading.Value & "' WHERE (([Entrants_Temp_Split].[Person Code2] = " &
Me.dummy_person_code.Value & ") AND ([Entrants_Temp_Split].[Type] =
""Reading""));"

In case the ]Person Code2] field is also Text, you need to do the same:

DoCmd.RunSQL "UPDATE [Entrants_Temp_Split] SET [Level] = '" &
Me.Reading.Value & "' WHERE (([Entrants_Temp_Split].[Person Code2] = '"
& Me.dummy_person_code.Value & "') AND ([Entrants_Temp_Split].[Type] =
""Reading""));"

Note: Single quotes work just fine as text delimiters in VBA, and
improve readability vs. double double quotes (no pun intended!).

HTH,
Nikos

I am having a problem with my update query.
Reading.value is a combo box on the same form as the button that executes
this query. It executes an insert query first which works fine, and then an
update query which also works fine. The update query below then returns
"syntax error (missing operator) in query expression 'Entry 1'."
'Entry 1' is the selected value of Me.Reading.

Am I missing something obvious??

DoCmd.RunSQL "UPDATE [Entrants_Temp_Split] SET [Level] = " &
Me.Reading.Value & " WHERE (([Entrants_Temp_Split].[Person Code2] = " &
Me.dummy_person_code.Value & ") AND ([Entrants_Temp_Split].[Type] =
""Reading""));"