Start and End Date questions

[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

 

[Marshall Barton]

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?

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.

 

[Annelie]

It worked. I just did not know how to use DMax.
Thank you.
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?

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.