query to pull between two dates

[AccessNoviceButTrying]

Hi All,

Have a question I was hoping someone could help me with.

I have a table which has a field to capture close dates.

I also have a form which allows user to enter a "to" and "from" date.

I want to the query to pull all the records that have a closed date between
the "to" and "from" dates the user has selected or to pull all the records if
they leave it blank.

Between Nz([Forms]![frmadHocReports]![TODate],[tblMainIDDate]) And
Nz([Forms]![frmAdHocReports]![FromDAte],[tblMainIDDate])

The above code works to pull records that fall between the dates when
entered but if the "to" and "from" fields are left blank, the query pulls all
the records but only if it has a close date. I want the query to pull all the
records, even if it doesnt have a close date and it blank.

Any help would be greatly appreciated.

Thanks

 

[Marshall Barton]

I have a table which has a field to capture close dates.

I also have a form which allows user to enter a "to" and "from" date.

I want to the query to pull all the records that have a closed date between
the "to" and "from" dates the user has selected or to pull all the records if
they leave it blank.

Between Nz([Forms]![frmadHocReports]![TODate],[tblMainIDDate]) And
Nz([Forms]![frmAdHocReports]![FromDAte],[tblMainIDDate])

The above code works to pull records that fall between the dates when
entered but if the "to" and "from" fields are left blank, the query pulls all
the records but only if it has a close date. I want the query to pull all the
records, even if it doesnt have a close date and it blank.

This is less than ideal because it would be better to skip
the criteria altogether when the From/To dates are missing:

Between Forms!frmadHocReports!TODate
And Forms!frmAdHocReports!FromDate
OR (Forms!frmadHocReports!TODate Is Null
And Forms!frmAdHocReports!FromDate Is Null)

It would be better to use code to construct the query's SQL
statement with or without the Between criteria and do
whatever(??) with the SQL statement.

 

[KARL DEWEY]

Try this --
(Between [Forms]![frmadHocReports]![TODate] And
[Forms]![frmAdHocReports]![FromDAte]) OR Is Null OR
[Forms]![frmadHocReports]![TODate] Is Null OR
[Forms]![frmAdHocReports]![FromDAte] Is Null

 

[AccessNoviceButTrying]

Thanks for the reply.

I tried the expression below but when I enter dates, it pulls all the
records between the dates given AND all the blank records.

I need all the records when the dates are blank or the records between the
dates when the dates are entered.

Thanks again.

Try this --
(Between [Forms]![frmadHocReports]![TODate] And
[Forms]![frmAdHocReports]![FromDAte]) OR Is Null OR
[Forms]![frmadHocReports]![TODate] Is Null OR
[Forms]![frmAdHocReports]![FromDAte] Is Null

--
Build a little, test a little.

Hi All,

Have a question I was hoping someone could help me with.

I have a table which has a field to capture close dates.

I also have a form which allows user to enter a "to" and "from" date.

I want to the query to pull all the records that have a closed date between
the "to" and "from" dates the user has selected or to pull all the records if
they leave it blank.

Between Nz([Forms]![frmadHocReports]![TODate],[tblMainIDDate]) And
Nz([Forms]![frmAdHocReports]![FromDAte],[tblMainIDDate])

The above code works to pull records that fall between the dates when
entered but if the "to" and "from" fields are left blank, the query pulls all
the records but only if it has a close date. I want the query to pull all the
records, even if it doesnt have a close date and it blank.

Any help would be greatly appreciated.

Thanks

 

[John Spencer]

Try

([CloseDateField] >=[Forms]![frmadHocReports]![TODate]
Or [Forms]![frmadHocReports]![TODate] is NULL)
AND
([CloseDateField]<=[Forms]![frmAdHocReports]![FromDate] or
[Forms]![frmAdHocReports]![FromDate] is Null)

You can enter that in query design view as criteria in one criteria "cell"
(>=[Forms]![frmadHocReports]![TODate]
Or [Forms]![frmadHocReports]![TODate] is NULL)
AND
(<=[Forms]![frmAdHocReports]![FromDate] or
[Forms]![frmAdHocReports]![FromDate] is Null)

Access will restructure that when you save it into something that looks (and
is) more complex.

An alternative (slower) but easier to understand
Field: SearchDate: Nz([CloseDateField],#1/1/1800#)
Criteria: Between Nz([Forms]![frmadHocReports]![ToDate],#1/1/1800#) And
Nz([Forms]![frmAdHocReports]![FromDate],#1/1/3999#)

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

Thanks for the reply.

I tried the expression below but when I enter dates, it pulls all the
records between the dates given AND all the blank records.

I need all the records when the dates are blank or the records between the
dates when the dates are entered.

Thanks again.

Try this --
(Between [Forms]![frmadHocReports]![TODate] And
[Forms]![frmAdHocReports]![FromDAte]) OR Is Null OR
[Forms]![frmadHocReports]![TODate] Is Null OR
[Forms]![frmAdHocReports]![FromDAte] Is Null

--
Build a little, test a little.

Hi All,

Have a question I was hoping someone could help me with.

I have a table which has a field to capture close dates.

I also have a form which allows user to enter a "to" and "from" date.

I want to the query to pull all the records that have a closed date between
the "to" and "from" dates the user has selected or to pull all the records if
they leave it blank.

Between Nz([Forms]![frmadHocReports]![TODate],[tblMainIDDate]) And
Nz([Forms]![frmAdHocReports]![FromDAte],[tblMainIDDate])

The above code works to pull records that fall between the dates when
entered but if the "to" and "from" fields are left blank, the query pulls all
the records but only if it has a close date. I want the query to pull all the
records, even if it doesnt have a close date and it blank.

Any help would be greatly appreciated.

Thanks