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


P

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?
 
Ad

Advertisements

D

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#)
 
P

Palto Fondberg

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


Douglas J. Steele said:
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!)


Palto Fondberg said:
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?
 
P

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?



Douglas J. Steele said:
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!)


Palto Fondberg said:
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?
 
D

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!)


Palto Fondberg said:
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?



Douglas J. Steele said:
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!)


Palto Fondberg said:
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?
 
Ad

Advertisements

P

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!


Douglas J. Steele said:
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!)


Palto Fondberg said:
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?



Douglas J. Steele said:
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?
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top