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 OK
This does not work using 2nd statement as OR:
ASKER
Sorry, I should have used a department code as example. I tried to simplify it.
This is what I need using VBA Code
I am getting this when I use OR in my code;
I know this is not logically correct but it shows what I need, with AND being an OR
This is what I need using VBA Code
I am getting this when I use OR in my code;
I know this is not logically correct but it shows what I need, with AND being an OR
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The response was excellent and helped solve my issue.
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.