VBA - create SQL statement using selections from Listbox as parameters (1 Viewer)

[Ginny2222]

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'.

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:

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)

and here is the result when I debug.print

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

 

[JHB]

Normally text is surrounded by a ':

strProducts = "SELECT MyTable.Field1, MyTable.Field2, MyTable.Field3 " & _
"FROM MyTable " & _
"WHERE (((MyTable.Field1) = '" & [Forms]![Startup]![fnADOComboboxSetComp].[Value] & "') AND ((MyTable.Field2) = '" & Criteria1 & "'))"

 

[Ginny2222]

Thanks JHB for your reply. My statement gets assigned to the variable strProducts and works perfectly without the extra quotas when I type the statement myself, but when I use Access Listbox within the statement it is adding the extra quotas and my coding fails!! I'm stuck on it.

I'm away for the next couple of weeks, so will try to think of a way around it. If you have any other suggestions I'd appreciate the help.

Thanks again,
rgs
Ginny

 

[JHB]

Could you show all the code what you've where you try to create the query on the fly.

 

[Uncle Gizmo]

I've seen something similar when passing function parameters by ref. If you have such values, change them to byVal...

 

[Ginny2222]

Hi all,
I seem to have solved the problem when creating a cut-down version of what I need. This appears to work. I think I was making it too complicated and adding in extra to the wording of the WHERE statement!! All I needed was the variables. Now I just need to replicate this for my actual database. Here's hoping

View attachment test_recordset.accdb

View attachment TestTemplate.zip

The .zip file contains the Excel file I was using TestTemplate.xlsm (I couldn't upload it in it's original file type). The database calls the file from "C:\Temp\TestTemplate.xlsm"

This really has helped. I was getting bogged down.
rgs
Ginny

 

[JHB]

Then you've solved the problem?

 

[Ginny2222]

Hi JHB
Not sure yet, I hope so. I'm working on my code in my actual database. I'll close the thread when I get it working.

Thanks for your time.
Much appreciated.
rgs
Ginny

 

[JHB]

If you get problem, then try to replace the below line:

Criteria = Criteria & "Customers.[Company]= " & Chr(34) _
& Me!
[List0].ItemData(i) & Chr(34)

With this:

Criteria = Criteria & "Customers.[Company]= '" _ 
& Me!
[List0].ItemData(i) & "'"

 

[Ginny2222]

Yoo Hoo - worked. Thanks to all for giving your time and thoughts on this. If anyone wants to see the expanded code where criteria from more than 1 Listbox is used I'm attaching it here.
View attachment test_recordset.accdb
This is probably only beginner stuff, but it's brought me one step further in my knowledge of DBs.

If anyone knows a better way of managing this let me know.

Thanks
Ginny