Running SQL statements in VBA

[troubledstudent]

I am constantly getting an error when I run the following sql statement in
docmd runsql:

dim sqlcheck as string
Dim db As DAO.Database
Dim rs As DAO.Recordset
db = currentdb

sqlcheck = "SELECT Processing.Retort_id, Processing.labor_code,
Processing.process_name, Processing.employee_name " & _
"FROM Processing WHERE (((Processing.Retort_id)=" &
Me.retortid.Value & ") AND ((Processing.labor_code)=" & Me!select_labor & ")
AND ((Processing.process_name)=" & Me!select_process & ") " & _
"AND ((Processing.employee_name)=" & Me!select_employee & "))"

Set rs = db.OpenRecordset(sqlcheck)
---------------------------
I am constantly getting an '3075' runtime error
"syntax error missing operator in query expression"

any advice?

thanks

 

[Allen Browne]

Test the values for Null before you build you SQL string.

For exampe, if the retortid control on the form is null, when you
concatenate it into the string you will get:
... WHERE (((Processing.Retort_id)=) AND
which is not going to work.

To help you see what's wrong, add the line:
Debug.Print sqlcheck
just before the "Set rs = ..." line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Douglas J. Steele]

In addition to what Allen pointed out, what are the data types of your
fields?

From their names, I would assume that at least process_name and
employee_name are text fields, which means you need to enclose the value
being passed in quotes:

sqlcheck = "SELECT Processing.Retort_id, Processing.labor_code, " & _
"Processing.process_name, Processing.employee_name " & _
"FROM Processing WHERE (((Processing.Retort_id)=" & _
Me.retortid.Value & ") AND " & _
"((Processing.labor_code)=" & Me!select_labor & ") " & _
"AND ((Processing.process_name)=""" & Me!select_process & """) " & _
"AND ((Processing.employee_name)=""" & Me!select_employee & """))"

Note the three double quotes in a row before and after Me!select_process and
Me!select_employee.

 

[troubledstudent]

I am aware of this. My code which does this was omitted. Thanks for the
insight.

 

[troubledstudent]

Yes that was the problem. Works great! My team thanks you.

D