Experts:
I am using 2 listboxes to pass values into a query. For my query #1, the code executes w/o any issues and re-creates the query each time based on the listbox values.
WHERE clause from Query #1:
======================
Code:
WHERE (SOURCE_FILE='Alpha' AND PRODUCT_TABLE='Other') OR (SOURCE_FILE='EXTRA' AND PRODUCT_TABLE='Other');
Replaced with dynamic strWhere (this one works fine!)
====================================
Code:
strWhere = "WHERE (SOURCE_FILE = " & strListboxSource & " And PRODUCT_TABLE = " & strListboxProduct & ") " & _
"Or (SOURCE_FILE = 'EXTRA' And PRODUCT_TABLE = " & strListboxProduct & ")"
However, for query #2, I get an error indicating that I may have an additional "'" (or something else) in my strSQL2 statement. Below SQL is what I want to end up with:
SQL from Query #2:
=============
Code:
SELECT DEMOGRAPHICS.Name, qry01_FieldsUsed.AFSAS_FIELDNAME
FROM DEMOGRAPHICS LEFT JOIN qry01_FieldsUsed ON DEMOGRAPHICS.Name = qry01_FieldsUsed.AFSAS_FIELDNAME
WHERE qry01_FieldsUsed.AFSAS_FIELDNAME Is Null;
Unfortunately, when replacing the word DEMOGRAPHICS with **strListboxProduct**, the routine fails and I get the error as depiced in the attached snapshot.
Replaced with dynamic SQL (this one fails!!!)
==============================
Code:
strSQL2 = "SELECT " & strListboxProduct & ".Name, qry01_FieldsUsed.AFSAS_FIELDNAME " & _
"FROM " & strListboxProduct & " LEFT JOIN qry01_FieldsUsed ON " & strListboxProduct & ".Name = qry01_FieldsUsed.AFSAS_FIELDNAME " & _
"WHERE qry01_FieldsUsed.AFSAS_FIELDNAME Is Null;"
Right now, the value for strListboxProduct = "'Demographics'". Given that DEMOGRAPHICS <> 'DEMOGRAPHICS', I believe that's why I'm getting the error.
My question: If that's indeed the cause for the failure, how can I strip the " ' " (i.e., single quote) from strListboxProduct (even though I do need to include " ' " for my 1st query)?
Thank you,
Tom
P.S. Please see attached sample DB in the attached zip file. Please be aware that when running it via form "F01_MainMenu" ("Run Queries"), the 2nd query "qry02_FieldsMissing" will be deleted by the VBA.
Thus, I included "qry02_FieldsMissing_Backup" in order to quickly copy/paste the object and rename it again as "qry02_FieldsMissing".