Syntax error (missing operator) in query expression

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

Guest

Agent is a Long number

Set rs = CurrentDb.OpenRecordset("Select * From [Agent Maintenance] where
[Agent] =" & [Agent], dbOpenDynaset)

If I type a value in the Agent field it works fine. But, if I tab through
the field, without entering a value, i get the error.

any ideas why this is happening?
 
Well, if there's no value in the 'Agent' control, the SQL statement will end
up looking like this ...

"SELECT * FROM [Agent Maintenance] WHERE [Agent] ="

.... and that is not a valid SQL statement - something has to come after the
'=' sign.

The solution depends on what you want to happen when there is no value in
the 'Agent' control. If you want to return records where the Agent field is
Null, you can use something like ...

If Len(Agent & vbNullString) = 0 Then
strSQL = "SELECT * FROM [Agent Maintenance] WHERE Agent IS NULL"
Else
strSQL = "SELECT * FROM [Agent Maintenance] WHERE Agent = " & Agent
End If
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
 
Back
Top