Query Criteria from Forms

[Carol]

First, thanks in advance for your help!

I want to create a query that will collect criteria from a
form to use to give me the results I want for several
different sets of circumstances.

I have run into some problems, though. For instance, I
have a StartDate and EndDate field on the form. The
criteria experssion I used is "Between[forms]![formname]!
[StartDate]and[forms]![formname]![EndDate]." This works
fine if I specify dates, but if not, I get no results. So
I added and "or" criteria to specify either of these
fields could also be null. That works great. But when I
do this with every field, I get no results.

Can someone tell me what I'm doing wrong or if there's a
better way to do this?

Thanks,
Carol

 

[MGFoster]

First, thanks in advance for your help!

I want to create a query that will collect criteria from a
form to use to give me the results I want for several
different sets of circumstances.

I have run into some problems, though. For instance, I
have a StartDate and EndDate field on the form. The
criteria experssion I used is "Between[forms]![formname]!
[StartDate]and[forms]![formname]![EndDate]." This works
fine if I specify dates, but if not, I get no results. So
I added and "or" criteria to specify either of these
fields could also be null. That works great. But when I
do this with every field, I get no results.

Can someone tell me what I'm doing wrong or if there's a
better way to do this?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I've had success w/ substituting defaults when the form's control value
is NULL. E.g. (SQL WHERE clause):

WHERE DateColumn Between Nz(Forms!FormName!txtBegin,#12/30/1899#) And
Nz(Forms!FormName!txtEnd, #12/31/9999#)

This will substituted the defaults of:

Begin Date: Dec 30, 1899
End Date: Dec 31, 9999

In effect, if both form controls are null, all records between the
default dates (usually all records in table) will returned. If the
Begin was empty and the End = Jan 15, 2003, the records returned would
all have dates <= Jan 15, 2003. Etc.

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQHSmz4echKqOuFEgEQK5NQCdGGPcXoaN2QMtEinClO6wElvaw0AAn3EJ
Z9uvKwtw+tPb4umrJkjXBn6U
=PpyH
-----END PGP SIGNATURE-----