Querying table using duel date fields

[Stewart Allen]

I'm trying to filter a table that has 2 date fields, the first date will
always have a value but the second will only occasionally has a value. Each
date field also has a corresponding text field to record the record's
status.

Sample data:

*tblTest*
Model Parts CDate CStatus RDate RStatus
616 $359.79 03-Nov-03 C
616 $109.56 18-Nov-03 C
616 $0.00 20-Jan-04 D 16-Feb-04 C
620 $8.75 07-Jan-04 C 17-Feb-04 D
624 $41.23 24-Oct-03 C 17-Feb-04 D
624 $340.39 10-Feb-04 C 17-Feb-04 D
626 $371.64 28-Oct-03 C
624 $204.98 19-Feb-04 C

What I want to do is display all records between 15-Oct-03 and 18-Feb-04
that have a RStatus of "C" or a CStatus of "C" if RStatus is null. This will
show records 1, 2, 3 & 7. If I change the filter to between 15-Oct-03 and
15-Feb-04 it should show records 1, 2, 4, 5, 6 & 7. Changing the filter to
between 10-Feb-04 and 20-Feb-04 should show records 3 & 8.

My criteria is that is the date in the RDate field does not fall inside the
date range or the RDate field is null, the test should then apply to the
CDate and CStatus fields.

Can somebody guide me in how to create a query to do this. The dates used
for the filtering will come from an open form.

Stewart

[Allen Browne]

The Nz() function allows you to specify a value to use if one is null.
Assuming that RDate and RStatus with be Null at the same time (i.e. either
both Null, or neither Null), your SQL statement could be:

WHERE (Nz([RStatus], [CStatus]) = 'C') AND
(Nz([RDate], [CDate]) Between
Forms!MyForm!StartDate And Forms!MyForm!EndDate)

If you place literal dates into the SQL statement, they must be in the
mm/dd/yyyy format. More info on that in:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Stewart Allen" <sa****@ThisPartNotVailid.wave.co.nz> wrote in message
news:c1**********@news.wave.co.nz...

I'm trying to filter a table that has 2 date fields, the first date will
always have a value but the second will only occasionally has a value. Each date field also has a corresponding text field to record the record's
status.

Sample data:

*tblTest*
Model Parts CDate CStatus RDate RStatus
616 $359.79 03-Nov-03 C
616 $109.56 18-Nov-03 C
616 $0.00 20-Jan-04 D 16-Feb-04 C
620 $8.75 07-Jan-04 C 17-Feb-04 D
624 $41.23 24-Oct-03 C 17-Feb-04 D
624 $340.39 10-Feb-04 C 17-Feb-04 D
626 $371.64 28-Oct-03 C
624 $204.98 19-Feb-04 C

What I want to do is display all records between 15-Oct-03 and 18-Feb-04
that have a RStatus of "C" or a CStatus of "C" if RStatus is null. This will show records 1, 2, 3 & 7. If I change the filter to between 15-Oct-03 and
15-Feb-04 it should show records 1, 2, 4, 5, 6 & 7. Changing the filter to
between 10-Feb-04 and 20-Feb-04 should show records 3 & 8.

My criteria is that is the date in the RDate field does not fall inside the date range or the RDate field is null, the test should then apply to the
CDate and CStatus fields.

Can somebody guide me in how to create a query to do this. The dates used
for the filtering will come from an open form.

Stewart

[Stewart Allen]

Thanks Allen, it was a simpler solution than what I was using but it still
doesn't pull the required records. If the RDate falls outside the date
criteria (even if it's not null), the filter should use the CDate.

Rec CDate CStatus RDate RStatus
1 03-Dec-03 C
2 20-Jan-04 D 16-Feb-04 C
3 07-Jan-04 C 17-Feb-04 D

A date range of 1-Dec-03 to 16-Feb-04 with 'C' as the status should pull
records 1 & 3.
A date range of 1-Dec-03 to 17-Feb-04 with 'C' as the status should pull
records 1 & 2.
If the RDate Is Null then the RStatus Is Null just as you said but the
filter should test the date in the CDate and the CStatus if the RDate Is Not
Null and the RDate falls outside the filter range.

I know this is a tough one but any help would be appreciated.

Stewart
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<40***********************@freenews.iinet.net .au>...

The Nz() function allows you to specify a value to use if one is null.
Assuming that RDate and RStatus with be Null at the same time (i.e. either
both Null, or neither Null), your SQL statement could be:

WHERE (Nz([RStatus], [CStatus]) = 'C') AND
(Nz([RDate], [CDate]) Between
Forms!MyForm!StartDate And Forms!MyForm!EndDate)

If you place literal dates into the SQL statement, they must be in the
mm/dd/yyyy format. More info on that in:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Stewart Allen" <sa****@ThisPartNotVailid.wave.co.nz> wrote in message
news:c1**********@news.wave.co.nz...

I'm trying to filter a table that has 2 date fields, the first date will
always have a value but the second will only occasionally has a value.

Each

date field also has a corresponding text field to record the record's
status.

Sample data:

*tblTest*
Model Parts CDate CStatus RDate RStatus
616 $359.79 03-Nov-03 C
616 $109.56 18-Nov-03 C
616 $0.00 20-Jan-04 D 16-Feb-04 C
620 $8.75 07-Jan-04 C 17-Feb-04 D
624 $41.23 24-Oct-03 C 17-Feb-04 D
624 $340.39 10-Feb-04 C 17-Feb-04 D
626 $371.64 28-Oct-03 C
624 $204.98 19-Feb-04 C

What I want to do is display all records between 15-Oct-03 and 18-Feb-04
that have a RStatus of "C" or a CStatus of "C" if RStatus is null. This

will

show records 1, 2, 3 & 7. If I change the filter to between 15-Oct-03 and
15-Feb-04 it should show records 1, 2, 4, 5, 6 & 7. Changing the filter to
between 10-Feb-04 and 20-Feb-04 should show records 3 & 8.

My criteria is that is the date in the RDate field does not fall inside

the

date range or the RDate field is null, the test should then apply to the
CDate and CStatus fields.

Can somebody guide me in how to create a query to do this. The dates used
for the filtering will come from an open form.

Stewart

[Allen Browne]

To filter on the CDate field if RDate is null or ourside the range, your
WHERE clause would include:

WHERE IIf(([RDate] >= Forms!MyForm!StartDate) AND ([RDate] <=
Forms!MyForm!EndDate), [RDate], [CDate])
Between Forms!MyForm!StartDate And Forms!MyForm!EndDate

The Immediate Iff() chooses the RDate if the its first argument is True.
Otherwise (False or Null), it chooses the CDate field.
The result is then used for the Between operator.

I'll let you add the Status bit.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Stewart Allen" <sa****@wave.co.nz> wrote in message
news:23*************************@posting.google.co m...

Thanks Allen, it was a simpler solution than what I was using but it still
doesn't pull the required records. If the RDate falls outside the date
criteria (even if it's not null), the filter should use the CDate.

Rec CDate CStatus RDate RStatus
1 03-Dec-03 C
2 20-Jan-04 D 16-Feb-04 C
3 07-Jan-04 C 17-Feb-04 D

A date range of 1-Dec-03 to 16-Feb-04 with 'C' as the status should pull
records 1 & 3.
A date range of 1-Dec-03 to 17-Feb-04 with 'C' as the status should pull
records 1 & 2.
If the RDate Is Null then the RStatus Is Null just as you said but the
filter should test the date in the CDate and the CStatus if the RDate Is Not Null and the RDate falls outside the filter range.

I know this is a tough one but any help would be appreciated.

Stewart
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message

news:<40***********************@freenews.iinet.net .au>...

The Nz() function allows you to specify a value to use if one is null.
Assuming that RDate and RStatus with be Null at the same time (i.e. either both Null, or neither Null), your SQL statement could be:

WHERE (Nz([RStatus], [CStatus]) = 'C') AND
(Nz([RDate], [CDate]) Between
Forms!MyForm!StartDate And Forms!MyForm!EndDate)

If you place literal dates into the SQL statement, they must be in the
mm/dd/yyyy format. More info on that in:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

"Stewart Allen" <sa****@ThisPartNotVailid.wave.co.nz> wrote in message
news:c1**********@news.wave.co.nz...

I'm trying to filter a table that has 2 date fields, the first date will always have a value but the second will only occasionally has a value.

Each

date field also has a corresponding text field to record the record's
status.

Sample data:

*tblTest*
Model Parts CDate CStatus RDate RStatus
616 $359.79 03-Nov-03 C
616 $109.56 18-Nov-03 C
616 $0.00 20-Jan-04 D 16-Feb-04 C
620 $8.75 07-Jan-04 C 17-Feb-04 D
624 $41.23 24-Oct-03 C 17-Feb-04 D
624 $340.39 10-Feb-04 C 17-Feb-04 D
626 $371.64 28-Oct-03 C
624 $204.98 19-Feb-04 C

What I want to do is display all records between 15-Oct-03 and 18-Feb-04 that have a RStatus of "C" or a CStatus of "C" if RStatus is null. This

will

show records 1, 2, 3 & 7. If I change the filter to between 15-Oct-03

and 15-Feb-04 it should show records 1, 2, 4, 5, 6 & 7. Changing the filter to between 10-Feb-04 and 20-Feb-04 should show records 3 & 8.

My criteria is that is the date in the RDate field does not fall inside the

date range or the RDate field is null, the test should then apply to

the CDate and CStatus fields.

Can somebody guide me in how to create a query to do this. The dates used for the filtering will come from an open form.

Stewart

[Stewart Allen]

Thanks Allen. After a bit of playing around I got the Status part
working as well and the whole thing works corectly.

Stewart
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<40***********************@freenews.iinet.net .au>...

To filter on the CDate field if RDate is null or ourside the range, your
WHERE clause would include:

WHERE IIf(([RDate] >= Forms!MyForm!StartDate) AND ([RDate] <=
Forms!MyForm!EndDate), [RDate], [CDate])
Between Forms!MyForm!StartDate And Forms!MyForm!EndDate

The Immediate Iff() chooses the RDate if the its first argument is True.
Otherwise (False or Null), it chooses the CDate field.
The result is then used for the Between operator.

I'll let you add the Status bit.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.