Link to home
Get AccessLog in
Avatar of ANTHONY CHRISTI
ANTHONY CHRISTIFlag for United States of America

asked on

MS Access VBA code generating a dynamic query with AND / Or parameters.

In VBA I am Generating a Dynamic Query based on criteria entered on a form.  When using the AND the query adds the 2nd field to the 2nd column and it works OK. 

Example: If [Combo508] <> " " Then
    where = where & "(([PeriodFrom1] >= """ & [Combo508] & """))"

   Endif

If [Combo510] <> " " Then
     where = where & " and (([PeriodTo1] <= """ & [Combo557] & """))"
 End If

This works OKUser generated image

This does not work using 2nd statement as OR:

User generated image


Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

What doesn't work?
You don't get the data you expect?

You are asking for all records with a

Period from date >=20210120

OR

with a period to date <=20210127

 and by the way, if this is a date time field, the correct delimiter is a #

>=#01/20/2021#

 and it should be in the US date format of MM/DD/YY  or MM/DD/YYYY

Jim.


Avatar of ANTHONY CHRISTI

ASKER

Sorry, I should have used a department code as example.  I tried to simplify it.
This is what I need using VBA Code
User generated image
I am getting this when I use OR in my code;
User generated image
I know this is not logically correct but it shows what I need, with AND being an OR
User generated image

I can have up to 30 department codes depending on what the user enters.


<<I know this is not logically correct but it shows what I need, with AND being an OR >>

 Actually that will work.   Click on view/SQL view in query design and you will see the resulting statement that you need.

 but, you may want to use the IN() operator for the dept code:

If [Combo508] <> " " Then
    where = where & "(([PeriodFrom1] >= """ & [Combo508] & """))"
   Endif
If [Combo510] <> " " Then
     where = where & " AND (([PeriodTo1] <= """ & [Combo557] & """))"
 End If

If [Comboxxx] <> " " Then
    where = where & " AND ([DEPCOD] IN("  & chr$(34) & Me.[Comboxxx] & chr$(34) & ")"
Endif

Me.[Comboxxx] being a comma separated list of of values.   Have a look at:

https://www.w3schools.com/sql/sql_ref_in.asp

to see what I mean.  

Note also that it's somewhat of a convention to put the operators in caps so they are easier to spot and the Chr$(34) gives you a quote.   Also easier to read.

I'd also do the checks on the combo's like this:

 If NZ([Combo508],"")<>"" Then

Jim.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
The response was excellent and helped solve my issue.