Query Criteria: evaluating criteria as a group

[WildlyHarry]

I have a form that allows a user to select multiple criteria to search for
records in a table. I am trying to make the form as flexible as possible
and allow the user to use as many or as few criteria as they want. The
selections on the form are then fed as criteria to a query. I have been able
to accomplish much of what I set out to do, using statments like
[forms]![form1]![name] or [forms]![form1]![name] is null. The issue I am
having is when I try to set up a date range. I would normally use Between
[forms]![form1]![date1] and [forms]![form1]![date2], but the user will not
always input a date. So I tried various combinations of (Between
[forms]![form1]![date1] and [forms]![form1]![date2]) or
[forms]![form1]![date1] is null. With no success. Does anyone have any
suggestions on how to add a date range that may or may not be used in
conjunction with multiple other criteria? Thanks in advance for you help.

 

[Douglas J. Steele]

What do you want done if they don't input one or the other date? Assuming
that if they don't enter date1, you want everything that's less than or
equal to date2, if they don't enter date2, you want everything that's
greater than or equal to date1, and if they don't enter any date, you want
all records, you could try:

Between Nz([forms]![form1]![date1], #1/1/100#) And
Nz([forms]![form1]![date2], #12/31/9999#)

Note that any rows for which the date field being checked is null will not
be returned by that criteria.

 

[WildlyHarry]

Actually I want the date range to be optional and an all or nothing type
deal. If they enter a date1 there must be a date2, because the exact dates
will be unknown to the user. So I only want the query to look for a date
range when there is a date selected. Otherwise just ingore the dates and pay
attention to the other criteria.

What do you want done if they don't input one or the other date? Assuming
that if they don't enter date1, you want everything that's less than or
equal to date2, if they don't enter date2, you want everything that's
greater than or equal to date1, and if they don't enter any date, you want
all records, you could try:

Between Nz([forms]![form1]![date1], #1/1/100#) And
Nz([forms]![form1]![date2], #12/31/9999#)

Note that any rows for which the date field being checked is null will not
be returned by that criteria.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a form that allows a user to select multiple criteria to search for
records in a table. I am trying to make the form as flexible as possible
and allow the user to use as many or as few criteria as they want. The
selections on the form are then fed as criteria to a query. I have been
able
to accomplish much of what I set out to do, using statments like
[forms]![form1]![name] or [forms]![form1]![name] is null. The issue I am
having is when I try to set up a date range. I would normally use Between
[forms]![form1]![date1] and [forms]![form1]![date2], but the user will not
always input a date. So I tried various combinations of (Between
[forms]![form1]![date1] and [forms]![form1]![date2]) or
[forms]![form1]![date1] is null. With no success. Does anyone have any
suggestions on how to add a date range that may or may not be used in
conjunction with multiple other criteria? Thanks in advance for you help.

 

[Douglas J. Steele]

Actually, (Between [forms]![form1]![date1] And [forms]![form1]![date2]) Or
[forms]![form1]![date1] Is Null should work in that case (although you'd
need to go into the SQL view and double check that that resolves to
something like... ((DateField Between [forms]![form1]![date1] And
[forms]![form1]![date2]) Or [forms]![form1]![date1] Is Null), where
"DateField" is the appropriate name from your table)

Assuming it did resolve correctly and it's still not working, try

((DateField >= [forms]![form1]![date1] And DateField <=
[forms]![form1]![date2]) Or ([forms]![form1]![date1] Is Null))

If that's not working, perhaps you could indicate what "no success" means.
Do you get an error? If so, what's the error? If you don't get an error,
what's the symptom that causes you to call it not succesful? You do realize,
I hope, that what's in the fields must be in mm/dd/yyyy format regardless of
your regional settings (okay, this isn't strictly true: you can use
yyyy-mm-dd or even dd mmm yyyy. The point is, if you're using dd/mm/yyyy, it
isn't going to work). As well, if the date field in your table includes time
(usually because you used the Now function to populate it), you're likely
not going to pick up any records dated whatever's in date2.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Actually I want the date range to be optional and an all or nothing type
deal. If they enter a date1 there must be a date2, because the exact
dates
will be unknown to the user. So I only want the query to look for a date
range when there is a date selected. Otherwise just ingore the dates and
pay
attention to the other criteria.

What do you want done if they don't input one or the other date? Assuming
that if they don't enter date1, you want everything that's less than or
equal to date2, if they don't enter date2, you want everything that's
greater than or equal to date1, and if they don't enter any date, you
want
all records, you could try:

Between Nz([forms]![form1]![date1], #1/1/100#) And
Nz([forms]![form1]![date2], #12/31/9999#)

Note that any rows for which the date field being checked is null will
not
be returned by that criteria.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a form that allows a user to select multiple criteria to search
for
records in a table. I am trying to make the form as flexible as
possible
and allow the user to use as many or as few criteria as they want. The
selections on the form are then fed as criteria to a query. I have
been
able
to accomplish much of what I set out to do, using statments like
[forms]![form1]![name] or [forms]![form1]![name] is null. The issue I
am
having is when I try to set up a date range. I would normally use
Between
[forms]![form1]![date1] and [forms]![form1]![date2], but the user will
not
always input a date. So I tried various combinations of (Between
[forms]![form1]![date1] and [forms]![form1]![date2]) or
[forms]![form1]![date1] is null. With no success. Does anyone have
any
suggestions on how to add a date range that may or may not be used in
conjunction with multiple other criteria? Thanks in advance for you
help.