strWhere syntax error

[skydivetom]

Hello:



I need some assistance with tweaking my strWhere clause. Once I modified it, the 2 lines (as part of *Option #3) result in a syntax error. Here's what I have:

Option #1: Criteria for 2 fields [SOURCE_FILE] & [PRODUCT_TABLE]

strWhere = "WHERE [SOURCE_FILE] in (" & Left(strSource, Len(strSource) - 1) & ") AND [PRODUCT_TABLE] in (" & Left(strProduct, Len(strProduct) - 1) & ")"

... option #1 executes w/o any errors.


Option #2: Criteria for 2 fields [SOURCE_FILE] & [PRODUCT_TABLE] with multiple rows WHERE 'EXTRA' needs to remain hard-coded

strWhere = "WHERE ((([01_tbl_ProductTables].SOURCE_FILE) in (" & Left(strSource, Len(strSource) - 1) & ")) And (([01_tbl_ProductTables].PRODUCT_TABLE) = 'DEMOGRAPHICS')) " & _
                "Or ((([01_tbl_ProductTables].SOURCE_FILE) = 'EXTRA') And (([01_tbl_ProductTables].PRODUCT_TABLE) = 'DEMOGRAPHICS'))"

... option #2 executes w/o any errors.


Option #3: Criteria for 2 fields [SOURCE_FILE] & [PRODUCT_TABLE] with multiple rows WHERE 'EXTRA' needs to remain hard-coded but 'Demographics' needs to be dynamic

strWhere = "WHERE ((([01_tbl_ProductTables].SOURCE_FILE) in (" & Left(strSource, Len(strSource) - 1) & ")) And (([01_tbl_ProductTables].PRODUCT_TABLE) in (" & Left(strProduct, Len(strProduct) - 1) & ") " & _
               "Or ((([01_tbl_ProductTables].SOURCE_FILE) = 'EXTRA') And (([01_tbl_ProductTables].PRODUCT_TABLE) in (" & Left(strProduct, Len(strProduct) - 1) & ")"""

... option #3 results in syntax error upon execution. See attached JPG for details.


My question: How do I need to modify the VBA (i.e., add/remove parenthesis and/or other characters) so that option #3 code will execute?

Thank you,
Tom

[moke123]

I get an invalid attachment error when trying to download your test.zip file.

Whats the output when you debug.print the strWhere clause? Thats where you'll see the error.

[skydivetom]

moke123 -- I am currently away from my desk. I will re-post file when I return (1-2 hrs).

Thank you.

[skydivetom]

Did the attached Jpg not include the error message/feedback?

[Minty]

In addition to Moke's advice, when you are building a SQL string you don't need the million (((('s that Access insists on adding in Where clauses built in the Query designer.

Remove all but the ones you need, (to force an AND or OR clause) and you will see a clearer picture.
I think you have an extra "" soemwhere but it's difficult to see.

Also on really long SQL string I prefer to break them up into smaller chunks, it lets you see the query better.

strWhere = " Source = 1  " 
strWhere = strWhere  &" AND firstName = 'John' " 
strWhere = strWhere  &" AND MyDate = #" &  Me.Mystartdate & " # "
etc etc

This also has the benefit of being able to easily comment out any part of it to see where it's all gone wrong.

I would also put Left(strSource, Len(strSource) - 1) and Left(strProduct, Len(strProduct) - 1) into there own variables to tidy thing up and reduce the length of it all.

[orange]

I agree with Minty's approach --- " do the swhere build up in small bites'

[skydivetom]

moke123 -- I compacted/repaired DB. See attached zip. Hopefully this one works for you.

//

All -- thank you for chiming in on this post... I'll check out the recommendations.

[skydivetom]

Success!!! Removing the "million (((('s" helped tremendously! It now executes just fine.

I'm still working on breaking up the WHERE clause but the original problem has been solved. Thank you everyone!

[Minty]

Good to hear you have it sorted out.