SQL won't recognize '!' character

[batteam]

I am trying to write a where clause in a macro in a MSAccess 2007 project. I need to reference a control value on a form for my where criteria, like:
[participant_id]=[Forms]![frmGroup_Sales_MainData]![txtParticipantID]

However, I get "Incorrect syntax near '!' error. Anyboyd know what will work in this situation? Thanks for any help you can provide.

 

[SQLBill]

You need to post your question in a Forum for Microsoft Access. This forum is specifically for Microsoft's SQL Server.

Someone on this forum MIGHT be able to help you...but you'll get better help from the proper forum.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[philhege]

If you're constructing the SQL statement, you'll have to place the control reference outside the quotes, like

SQL = "SELECT * FROM MyTable WHERE [participant_id] = " & [Forms]![frmGroup_Sales_MainData]![txtParticipantID]

-----------
With business clients like mine, you'd be better off herding cats.

 

[batteam]

Thanks for the tip Philhege. That, and when I used the stmt as a report recordsource, I forgot that the where clause can be stated as: ...where participant_id = ?. When you use the ? character, the report knows to look down at the Input Parameters property box where the criteria can be entered. Thanks again.

 

[Olaf Doschke]

Yes, the ? syntax is for parameterized queries but instead of that and the input box you can also specify the parameter, so Access doesn't need to ask, by adding the value to the query string and not the expression, which SQL Server cannot evaluate. To add an integer into the query string you of course need to convert it to string. If you would need to provide a string value you would add single quote marks as string delimiter right before and after the [Form]...[txtName] or whatever expression is pointing to the value of a text valued control, eg

SQL = "SELECT * FROM Persons WHERE [participant.name] = '" & [Forms]![frmGroup_Person]![txtParticipantName] & "'", so the SQL Variable finally has the string SELECT * From Persons Where [participant.name] = 'Smith' and not the Access term for the control. SQL Server has no idea of your form, you send this query over to a process on another server in the general case and all SQL Server sees is the query string, not you environment and variables and object, so everything needed by SQL server has to be sent in. That's the difference to queries to the access database or table.

If you provide expressions in your SQL, they have to be T-SQL Expressions, eg you can Query "Select getdate()" to get the current server datetime, etc. But you can't pass in Expressions or terms, which only Access can evaluate.

Bye, Olaf.