Multi dated query

[Dan]

I put the criteria >(date()-7) in a query field. It gives me what I want
from that field. However, I have more date fields and would like them all
to show up in one report run from that query. But I have to run the report
4 times with the criteria above only in the date field I want in the report.

I could use these:
Date field 1: >(date()-7)
Date field 2 >(date()-14) or Is Null
Date field 3 >(date()-21) or Is Null
Date field 4 >(date()-28) or Is Null

However, the report always comes up blank.

I tried these:
Date field 1: >(date()-28)
Date field 2 >(date()-21) or Is Null
Date field 3 >(date()-14) or Is Null
Date field 4 >(date()-7) or Is Null

It still comes up wrong..

I want the report to include activity that occured in that record in the
past week and be included regardless of what is in the other date fields. I
don't a report with items that had no activity in the past week.

TIA
Dan

 

[Van T. Dinh]

Please describe the multiple date Fields you have in your source Table(s).

From your description, it sounds like you Table(s) have repeating groups of
Fields which may give you problems now and later - I think the problem you
described is one. In database theory, repeating groups of Field violates
the First Normal Form and in general, databases should meet the requirement
of Third Normal Form at least - there are 5 (or more) Normal Forms plus a
number of variations ...

 

[James A. Fortune]

I put the criteria >(date()-7) in a query field. It gives me what I want
from that field. However, I have more date fields and would like them all
to show up in one report run from that query. But I have to run the report
4 times with the criteria above only in the date field I want in the report.

I could use these:
Date field 1: >(date()-7)
Date field 2 >(date()-14) or Is Null
Date field 3 >(date()-21) or Is Null
Date field 4 >(date()-28) or Is Null

However, the report always comes up blank.

I tried these:
Date field 1: >(date()-28)
Date field 2 >(date()-21) or Is Null
Date field 3 >(date()-14) or Is Null
Date field 4 >(date()-7) or Is Null

It still comes up wrong..

I want the report to include activity that occured in that record in the
past week and be included regardless of what is in the other date fields. I
don't a report with items that had no activity in the past week.

TIA
Dan

Dan,

In the Query Builder (QBE), put the first criterion on line 1, the
second on line 2, etc. That will cause them to be OR'ed instead of
AND'ed. Also, I think it's better to use the DateAdd function to
subtract days from dates like this:

DateAdd("d", -28, Date())

instead of

Date() - 28

James A. Fortune
(e-mail address removed)

 

[Guest]

Dan,

In the Query Builder (QBE), put the first criterion on line 1, the
second on line 2, etc. That will cause them to be OR'ed instead of
AND'ed. Also, I think it's better to use the DateAdd function to
subtract days from dates like this:

DateAdd("d", -28, Date())

instead of

Date() - 28

yas htes ok