Query

P

Pass-the-reality

I have form with two fields. One is MR1stSent and the other is MRIn/Out. On
my query, I have both fields above listed. I wanted a third column that
indicates the greater of the two dates. For example if MR1stSent is 1-1-08
and MRIn/Out is 2-1-08, I want the third column on the query to say 2-1-08.
There will ALWAYS be a date in MR1stSent, but may not be a date in MRIn/Out.
In that case, I would want to show the MR1stSent date, as it would be the
greater date. Is there an IF statement I could use in the query or
something.
 
D

Douglas J. Steele

If both fields will always have values, try

IIf([MR1stSent] > [MRIn/Out], [MR1stSent], [MRIn/Out])

If one or the other (but not both) might not have a value, try:

IIf(Nz([MR1stSent],0) > Nz([MRIn/Out],0), [MR1stSent], [MRIn/Out])

If both can be missing a value, what do you want to see in the case where
they are both missing values? If, for example, you want today's date, you
could use:

IIf(IsNull([MR1stSent]) AND IsNull([MRIn/Out]), Date(), IIf(Nz([MR1stSent],
0) > Nz([MRIn/Out],0), [MR1stSent], [MRIn/Out]))
 
Top