troubleshooting Question

SQL statement

Avatar of ExpExchHelp
ExpExchHelpFlag for United States of America asked on
Microsoft Access
2 Comments1 Solution221 ViewsLast Modified:
I use the below SQL to dynamically recreate a query when clicking cmdButton (on a form).   Currently, I always add two fields [Efficiency] and [Suitability] as well as the "Is Null" into either field.  

I need to change the approach that those 2 fields will always be added.   Instead, I'd like to only add either/both fields to the query if their control values (listboxes) have selected values.

So, the following should happen:

1. If I select at least one value from either listbox (lstEffectiveness, lstSuitability), then add both fields [Effectiveness] and [Suitability] to the SQL and put "Is Null" into the WHERE... separated via OR statements.
2. If I select only lstEffectiveness, only add [Effectiveness] to the SQL and IsNull to the WHERE clause.
3. If I select only lstSuitability, only add [Suitability] to the SQL and IsNull to the WHERE clause.

Any suggestions how to dynamcially write this query?


    strSQL4 = "SELECT [02_qSel_Filter].*, [02_qSel_FilterSubEffectiveness].TrackNoIDpk, " & _
              "[02_qSel_FilterSubSuitability].TrackNoIDpk " & _
              "FROM (02_qSel_Filter LEFT JOIN 02_qSel_FilterSubEffectiveness ON " & _
              "[02_qSel_Filter].TrackNoIDpk = [02_qSel_FilterSubEffectiveness].TrackNoIDpk) " & _
              "LEFT JOIN 02_qSel_FilterSubSuitability ON [02_qSel_Filter].TrackNoIDpk = " & _
              "[02_qSel_FilterSubSuitability].TrackNoIDpk " & _              
              "WHERE ((([02_qSel_FilterSubEffectiveness].TrackNoIDpk) Is Not Null)) OR                  ((([02_qSel_FilterSubSuitability].TrackNoIDpk) Is Not Null));"

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros