Date Field Criteria on Form Left blank, - run for "ALL"?

[Palto Fondberg]

Hi

I've set up a report wherby, when particular dates are chosen, survey
information between these two dates is supplied. My query works fine, apart
from how to deal with the outcome of the date field criteria being left
blank. When these date fields are left blank [and as I expected] when I run
the query this returns no rows of results. What I want to do is to be able
to run "for all dates" if the date fields are left blank. Im reasonably
familiar with the '...if is like null..' and the fact that within access
'query mode' screen it will "re-shuffle" the look of a query if you use
certain 'OR' rows criteria.
Can someone give me some advice on this problem please. Would it be easier
just to work in SQL view for the code, and if so, can you gibve me an
example please?

 

[Douglas J. Steele]

Presumably you've currently got something like

BETWEEN Forms![NameOfForm]![DateFrom] AND Forms![NameOfForm]![DateTo]

as your criteria. Change that to

BETWEEN Nz(Forms![NameOfForm]![DateFrom], #1/1/100#) AND
Nz(Forms![NameOfForm]![DateTo], #12/31/9999#)

 

[Palto Fondberg]

Great, thanks Doug, I wasn't aware of the NZ function until now

Presumably you've currently got something like

BETWEEN Forms![NameOfForm]![DateFrom] AND Forms![NameOfForm]![DateTo]

as your criteria. Change that to

BETWEEN Nz(Forms![NameOfForm]![DateFrom], #1/1/100#) AND
Nz(Forms![NameOfForm]![DateTo], #12/31/9999#)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi

I've set up a report wherby, when particular dates are chosen, survey
information between these two dates is supplied. My query works fine,
apart from how to deal with the outcome of the date field criteria being
left blank. When these date fields are left blank [and as I expected]
when I run the query this returns no rows of results. What I want to do
is to be able to run "for all dates" if the date fields are left blank.
Im reasonably familiar with the '...if is like null..' and the fact that
within access 'query mode' screen it will "re-shuffle" the look of a
query if you use certain 'OR' rows criteria.
Can someone give me some advice on this problem please. Would it be
easier just to work in SQL view for the code, and if so, can you gibve me
an example please?

 

[Palto Fondberg]

Hi Doug

I'm having some problems here. My expression is now as follows:

Between
Nz("Forms]![frmChooseSurveyReport]![txtReportFromDate]",#01/01/2008#) And
Nz("Forms]![frmChooseSurveyReport]![txtReportToDate]",#01/01/2010#)

but I'm getting an error message "expression is typed incorrectly or its too
complex to be evaluated"

any ideas please?

Presumably you've currently got something like

BETWEEN Forms![NameOfForm]![DateFrom] AND Forms![NameOfForm]![DateTo]

as your criteria. Change that to

BETWEEN Nz(Forms![NameOfForm]![DateFrom], #1/1/100#) AND
Nz(Forms![NameOfForm]![DateTo], #12/31/9999#)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi

I've set up a report wherby, when particular dates are chosen, survey
information between these two dates is supplied. My query works fine,
apart from how to deal with the outcome of the date field criteria being
left blank. When these date fields are left blank [and as I expected]
when I run the query this returns no rows of results. What I want to do
is to be able to run "for all dates" if the date fields are left blank.
Im reasonably familiar with the '...if is like null..' and the fact that
within access 'query mode' screen it will "re-shuffle" the look of a
query if you use certain 'OR' rows criteria.
Can someone give me some advice on this problem please. Would it be
easier just to work in SQL view for the code, and if so, can you gibve me
an example please?

 

[Douglas J. Steele]

Get rid of the quotes around the field names.

Between Nz(Forms]![frmChooseSurveyReport]![txtReportFromDate],#01/01/2008#)
And
Nz(Forms]![frmChooseSurveyReport]![txtReportToDate],#01/01/2010#)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug

I'm having some problems here. My expression is now as follows:

but I'm getting an error message "expression is typed incorrectly or its
too complex to be evaluated"

any ideas please?

Presumably you've currently got something like

BETWEEN Forms![NameOfForm]![DateFrom] AND Forms![NameOfForm]![DateTo]

as your criteria. Change that to

BETWEEN Nz(Forms![NameOfForm]![DateFrom], #1/1/100#) AND
Nz(Forms![NameOfForm]![DateTo], #12/31/9999#)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi

I've set up a report wherby, when particular dates are chosen, survey
information between these two dates is supplied. My query works fine,
apart from how to deal with the outcome of the date field criteria being
left blank. When these date fields are left blank [and as I expected]
when I run the query this returns no rows of results. What I want to do
is to be able to run "for all dates" if the date fields are left blank.
Im reasonably familiar with the '...if is like null..' and the fact that
within access 'query mode' screen it will "re-shuffle" the look of a
query if you use certain 'OR' rows criteria.
Can someone give me some advice on this problem please. Would it be
easier just to work in SQL view for the code, and if so, can you gibve
me an example please?

 

[Palto Fondberg]

Hi Doug

yes, thanks - Access added these quotes in automatically - I had typed your
original suggestion in exactly as you had replied - but there was a typo -
some missing square brackets - the following works perfectly:

Between Nz([Forms]![frmChooseSurveyReport]![txtReportFromDate],#01/01/2008#)
And Nz([Forms]![frmChooseSurveyReport]![txtReportToDate],#01/01/2010#)


thanks for your help, hopefully this will help others out there too!

Get rid of the quotes around the field names.

Between
Nz(Forms]![frmChooseSurveyReport]![txtReportFromDate],#01/01/2008#) And
Nz(Forms]![frmChooseSurveyReport]![txtReportToDate],#01/01/2010#)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug

I'm having some problems here. My expression is now as follows:

but I'm getting an error message "expression is typed incorrectly or its
too complex to be evaluated"

any ideas please?

Presumably you've currently got something like

BETWEEN Forms![NameOfForm]![DateFrom] AND Forms![NameOfForm]![DateTo]

as your criteria. Change that to

BETWEEN Nz(Forms![NameOfForm]![DateFrom], #1/1/100#) AND
Nz(Forms![NameOfForm]![DateTo], #12/31/9999#)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi

I've set up a report wherby, when particular dates are chosen, survey
information between these two dates is supplied. My query works fine,
apart from how to deal with the outcome of the date field criteria
being left blank. When these date fields are left blank [and as I
expected] when I run the query this returns no rows of results. What I
want to do is to be able to run "for all dates" if the date fields are
left blank. Im reasonably familiar with the '...if is like null..' and
the fact that within access 'query mode' screen it will "re-shuffle"
the look of a query if you use certain 'OR' rows criteria.
Can someone give me some advice on this problem please. Would it be
easier just to work in SQL view for the code, and if so, can you gibve
me an example please?