Hi all,
I'm using MS Access 2007.
I am trying to create a SQL statement 'on the fly' in VBA to gather user information to populate a template which is created using an Excel Template.
The SQL statement queries a table to extract 3 fields. Field 1 populates a combobox on my form and 1 item is chosen from this list. Field 2 is linked to a listbox (multi-select extended) to allow more than 1 selection. Both Field 1 and Field 2 populate the WHERE section of the SQL Statement.
The following would be the query I am trying to achieve 'on the fly'.
The values are being generated using variables and when I debug.print the variable there are no double quotes around the displayed string
criteria1 = MyTable.Field2 = "Product1" OR MyTable.Field2 = "Product2"
Here is the Statement that is created when assigning it to a variable:
and here is the result when I debug.print
Where are the double quotes in the WHERE statement for Field2 entries coming from? Without the Quotes the coding is working perfectly! What am I doing wrong???
Help appreciated.
rgs
Ginny
I'm using MS Access 2007.
I am trying to create a SQL statement 'on the fly' in VBA to gather user information to populate a template which is created using an Excel Template.
The SQL statement queries a table to extract 3 fields. Field 1 populates a combobox on my form and 1 item is chosen from this list. Field 2 is linked to a listbox (multi-select extended) to allow more than 1 selection. Both Field 1 and Field 2 populate the WHERE section of the SQL Statement.
The following would be the query I am trying to achieve 'on the fly'.
Code:
SELECT MyTable.Field1, MyTable.Field2, MyTable.Field3
FROM MyTable
WHERE (((MyTable.Field1)="Company1") AND (((MyTable.Field2)="Product1") OR ((MyTable.Field2)="Product2")));
The values are being generated using variables and when I debug.print the variable there are no double quotes around the displayed string
criteria1 = MyTable.Field2 = "Product1" OR MyTable.Field2 = "Product2"
Here is the Statement that is created when assigning it to a variable:
Code:
strProducts = "SELECT MyTable.Field1, MyTable.Field2, MyTable.Field3 " & _
"FROM MyTable " & _
"WHERE (((MyTable.Field1) = """ & [Forms]![Startup]![fnADOComboboxSetComp].[Value] & """) AND ((MyTable.Field2) = """ & Criteria1 & """))"
Set rstProducts = myDB.OpenRecordset(strProducts)
Code:
When the SQL Statement is created I am getting extra Quotation Marks around the Field 2
SELECT MyTable.Field1, MyTable.Field2, MyTable.Field3
FROM MyTable
WHERE (((MyTable.Field1)="Company1") AND (("MyTable.Field2)="Product1"") OR (("MyTable.Field2)="Product2""));
Where are the double quotes in the WHERE statement for Field2 entries coming from? Without the Quotes the coding is working perfectly! What am I doing wrong???
Help appreciated.
rgs
Ginny