Access 2003 to SQL 2000 query using data from the form.

[Webkins]

I am using Access 2003 to connect to a SQL 2000 database. I am having trouble with the following query to populate a combo box on the form using the manager name stored in a text box also on the same form:

select work_order_number
from mwo.dbo.mwo
where ((manager_approved is null) and (manager = [Forms]![frm_main_menu].[person]))
order by work_order_number

I get the following error: ODBC call failed. Line 3: Incorrect syntax near '!'. (#170)

It works fine until I add the second condition: and manager = [Forms]![frm_main_menu].[person]

Any ideas or suggestions ? Thank you so much !

 

[Andrzejek]

I would guess your SELECT statement is in some kind of string:

strSQL = "select work_order_number " _
" from mwo.dbo.mwo " _
" where ((manager_approved is null) " _
" and (manager = [blue]'" & [/blue][Forms]![frm_main_menu].[person])) [blue]& "'"[/blue] _
" order by work_order_number"

Debug.Print strSQL

Have fun.

---- Andy

 

[Webkins]

Yes the data returned is string, but your example does not work in the SQL Pass-Thru Query. Is this even possible ? I am frustrated because this simple issue is holding up my project and I do not know how else to procede. Thank you all for your help

 

[Webkins]

Does anyone know if this is possible or do I need to look for other options. I want to use the form data .[person] with a SQL Pass-thru query.

Thanks so much.

 

[PHV]

A Pass-thru query can't be parametized, dot.
A workaround is to update the SQL property of the QueryDef object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886