select query including dates outside of specified range

[AccessNeophyte]

Hi all,

I have a query set up for my report that is supposed to return all
records where the Admit Date is before or equal to the selected date,
AND the Discharge Date is after the Selected Date.

In the criteria for the Admit Date field, I put:
=<[Requested Date]

In the criteria for the Discharge Date field, I put:
Is Null
or

[Requested Date]

The results for Discharge Date are fine. No records with a Discharge
Date before or equal to the Requested Date appear. Not so good for
Admit Dates. I get back all records with the appropriate Discharge
Dates, even those with Admit Dates AFTER the Requested Date. What am
I doing wrong?

Thanks!
Access Neophyte

 

[George Nicholson]

Just a guess:

If you are working in the query designer then I'm assuming that your
criteria for DischargeDate takes up 2 lines in the query designer since its
an OR condition.

Make sure that you ALSO copy the AdmitDate field criteria down to the 2nd
line as well. Each line has to have a complete set of the AND conditions
that you want applied. One criteria line doesn't "know" anything about any
other criteria line.

HTH,

Hi all,

I have a query set up for my report that is supposed to return all
records where the Admit Date is before or equal to the selected date,
AND the Discharge Date is after the Selected Date.

In the criteria for the Admit Date field, I put:
=<[Requested Date]

In the criteria for the Discharge Date field, I put:
Is Null
or

[Requested Date]

The results for Discharge Date are fine. No records with a Discharge
Date before or equal to the Requested Date appear. Not so good for
Admit Dates. I get back all records with the appropriate Discharge
Dates, even those with Admit Dates AFTER the Requested Date. What am
I doing wrong?

Thanks!
Access Neophyte

 

[AccessNeophyte]

Just a guess:

If you are working in the query designer then I'm assuming that your
criteria for DischargeDate takes up 2 lines in the query designer since its
an OR condition.

Make sure that you ALSO copy the AdmitDate field criteria down to the 2nd
line as well. Each line has to have a complete set of the AND conditions
that you want applied. One criteria line doesn't "know" anything about any
other criteria line.

HTH,

Hi all,

I have a query set up for my report that is supposed to return all
records where the Admit Date is before or equal to the selected date,
AND the Discharge Date is after the Selected Date.

In the criteria for the Admit Date field, I put:
=<[Requested Date]

In the criteria for the Discharge Date field, I put:
Is Null
or

[Requested Date]

The results for Discharge Date are fine. No records with a Discharge
Date before or equal to the Requested Date appear. Not so good for
Admit Dates. I get back all records with the appropriate Discharge
Dates, even those with Admit Dates AFTER the Requested Date. What am
I doing wrong?

Thanks!
Access Neophyte- Hide quoted text -

- Show quoted text -

Worked Great! I've been able to apply this solution to some of my
other queries, too.

Thanks so much!
Access Neophyte