Start and End Date questions

  • Thread starter Thread starter Annelie
  • Start date Start date
A

Annelie

In my query if have the following criteria in the WEDate field:
Between NZ([Start Date],#11/02/01#) And NZ([End Date],#12/31/9999#)

If the user, hits enter, I want to display in the start date field on the
report 11/02/01, which was easy to do because that is the earliest date. my
control source for Start Date is: =IIf(IsNull([start
date]),#11/02/02#,[start date])
The ending data gives me problems. What would my iff statement be, if I
wanted it to show the latest date in query (there are many entries with the
same date) as end date?
Annelie
 
Annelie said:
In my query if have the following criteria in the WEDate field:
Between NZ([Start Date],#11/02/01#) And NZ([End Date],#12/31/9999#)

If the user, hits enter, I want to display in the start date field on the
report 11/02/01, which was easy to do because that is the earliest date. my
control source for Start Date is: =IIf(IsNull([start
date]),#11/02/02#,[start date])
The ending data gives me problems. What would my iff statement be, if I
wanted it to show the latest date in query (there are many entries with the
same date) as end date?
Click to expand...


Try using the DMax function:

=IIf(IsNull([EndDate], DMax("WEDate","thetable"),[EndDate])

It gets (a lot?) more complicated if you are using
additional criteria to further filter the report's data.
 
It worked. I just did not know how to use DMax.
Thank you.
Annelie

Marshall Barton said:
Annelie said:
In my query if have the following criteria in the WEDate field:
Between NZ([Start Date],#11/02/01#) And NZ([End Date],#12/31/9999#)

If the user, hits enter, I want to display in the start date field on the
report 11/02/01, which was easy to do because that is the earliest date. my
control source for Start Date is: =IIf(IsNull([start
date]),#11/02/02#,[start date])
The ending data gives me problems. What would my iff statement be, if I
wanted it to show the latest date in query (there are many entries with the
same date) as end date?
Click to expand...


Try using the DMax function:

=IIf(IsNull([EndDate], DMax("WEDate","thetable"),[EndDate])

It gets (a lot?) more complicated if you are using
additional criteria to further filter the report's data.
Click to expand...
 
Back
Top