Query Syntax for SQL Table

jamie57

Registered User.
Local time
Today, 02:15
Joined
Aug 23, 2002
Messages
15
We have a SQL back end, and an Access data project as the front end. We have a form that displays customer info from a customer table, then we are trying to have a list box with data from another table but ONLY the data that corresponds to that CustomerID. CustomerID is in both tables and relationship has been set. I need to use a value from the form in the query to do this, so we tried this:

SELECT Notes
FROM tblCustomerComments
WHERE CustomerID = [Forms]![frmCustomer]![CustomerID]

which from what I have read everywhere is the corret way. But I get an error everytime. (Error in WHERE clause near '!'.
Unable to parse query text.) , and if I save it and go back, it put spaces like this:

SELECT Notes
FROM tblCustomerComments
WHERE CustomerID = [Forms] ! [frmCustomer] ! [CustomerID]

I've tried without the square brackets, same problem. If I try to put it in the criteria field for CustomerID instead of editting the SQL directly, I get a Data Type Error in Expression. Any ideas?
 
If the query is an "Access" query, the syntax is correct. If the query is passed-through to SQL server, you cannot use this syntax. SQL server has no way to interpret the Forms! reference since it does not have access to your form object. To create a pass-through parameter query, you need to build it as a string in VBA where the parameter can be evaluated before the string is passed to the RDBMS for processing.

Dim strSQL as String
strSQL = "SELECT Notes FROM tblCustomerComments WHERE CustomerID = "
strSQL = strSQL & [Forms]![frmCustomer]![CustomerID]

Then use strSQL as your query.
 
Thank You. I am new to using VB in Access, though I have a lot of experience with VB outside of access. How do I use the string strSQL as the query for a listbox?
 

Users who are viewing this thread

Back
Top Bottom