Update query problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

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""));"
 
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
 
Superb. Can't believe I didn't work that out. Many thanks

Nikos Yannacopoulos said:
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""));"
Click to expand...
Click to expand...
 
Back
Top