Accept date from a form

[Jasper Recto]

I have a query that requires a date filter.

On the form, there is a Start Date field and an End Date field.

How can I make this query work if the user does the following.

1.) The user enters in a Start Date field but not an end date field.
Basically, I want the query to filter all items >= to the Start Date

2.) The user enters in a Start Date and an End Date. The query needs to
filter for records that are within the 2 date fields.


How can I add an item to the query so that it can accept they 2 options from
the form?

Thanks,
Jasper

 

[mscertified]

=NZ([StartDate],"1/1/1800") AND <= NZ(EndDate,"12/31/2999")

This will cater for a null start or end date.

-Dorian

 

[John Spencer]

While that should work, you do need to use # as the delimiter and not the
quote mark (").

=NZ([StartDate],#1/1/1800#) AND <= NZ([EndDate],#12/31/2999#)

That criteria will work AS LONG AS your field has a value other than null.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

=NZ([StartDate],"1/1/1800") AND <= NZ(EndDate,"12/31/2999")

This will cater for a null start or end date.

-Dorian

I have a query that requires a date filter.

On the form, there is a Start Date field and an End Date field.

How can I make this query work if the user does the following.

1.) The user enters in a Start Date field but not an end date field.
Basically, I want the query to filter all items >= to the Start Date

2.) The user enters in a Start Date and an End Date. The query needs to
filter for records that are within the 2 date fields.


How can I add an item to the query so that it can accept they 2 options from
the form?

Thanks,
Jasper

 

[Jasper Recto]

Thanks!!

Jasper

While that should work, you do need to use # as the delimiter and not the
quote mark (").

=NZ([StartDate],#1/1/1800#) AND <= NZ([EndDate],#12/31/2999#)

That criteria will work AS LONG AS your field has a value other than null.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

=NZ([StartDate],"1/1/1800") AND <= NZ(EndDate,"12/31/2999")

This will cater for a null start or end date.

I have a query that requires a date filter.

On the form, there is a Start Date field and an End Date field.

How can I make this query work if the user does the following.

1.) The user enters in a Start Date field but not an end date field.
Basically, I want the query to filter all items >= to the Start Date

2.) The user enters in a Start Date and an End Date. The query
needs to filter for records that are within the 2 date fields.


How can I add an item to the query so that it can accept they 2 options
from the form?

Thanks,
Jasper