Combobox reference in WHERE clause of view

[bobfin]

I have an Access project in which the main form is based on a view. The WHERE clause in the view needs to equate the value of a table field to the value of a combobox on the form. In the script that creates the view, I get a syntax error when the clause is:
WHERE Acct = [Forms]![frmMain]![cboAcct]
When I changed the clause to:
WHERE Acct = Me.cboAcct
I got no syntax error, but I got an error when I tried to run the script.
How should I code the script so I can create the view?

 

[boblarson]

If this is in a SQL string, it must be:

WHERE Acct = " & [Forms]![frmMain]![cboAcct]

so the whole string would be something like:

"Select OrderID from Orders " & _
"WHERE Acct = " & [Forms]![frmMain].[cboAcct] & ";"

 

[bobfin]

I changed the CREATE VIEW script for MS SQL Server 2000 so the WHERE clause is
WHERE Acct = " & [Forms]![frmMain].[cboAcct] & "
and got an "Invalid column name" error message when I ran the script.
How were the double quotes and ampersands supposed to fix the problem?

 

[boblarson]

Please post the whole query as that might help.

Also, the quotes and ampersands are necessary to include a reference to a control. One other thing - if the combobox result value is a string, you will need to have a single quote on the outside of the double quotes too.