indicator

[Guest]

I have the following columns in my query empid, courseA, datecompA, courseB,
datecompB, courseC, datecompC. What I need is an extra column that will have
an indicator Yes if any of the 2 dates(i.e., datecompA, datecompB, datecompC)
have dates, blank if all 3 dates have dates and No if only of the dates has
a date.

 

[Guest]

Not sure what the third criteria is, but you can use something like

Abs(IsNull(datecompA) + IsNull(datecompB) + IsNull(datecompC))

The IsNull will return -1 (True) Or 0 (False) if the date is empty.
The Abs will return the Absolute number
So, adding all the -1 will add up to the number of date field that are filled

IIf(Abs(IsNull(datecompA) + IsNull(datecompB) + IsNull(datecompC)) = 2
,"Yes",IIf(Abs(IsNull(datecompA) + IsNull(datecompB) + IsNull(datecompC)) = 3
, Null , "No"))

 

[Guest]

Thanks alot that worked perfect...

Not sure what the third criteria is, but you can use something like

Abs(IsNull(datecompA) + IsNull(datecompB) + IsNull(datecompC))

The IsNull will return -1 (True) Or 0 (False) if the date is empty.
The Abs will return the Absolute number
So, adding all the -1 will add up to the number of date field that are filled

IIf(Abs(IsNull(datecompA) + IsNull(datecompB) + IsNull(datecompC)) = 2
,"Yes",IIf(Abs(IsNull(datecompA) + IsNull(datecompB) + IsNull(datecompC)) = 3
, Null , "No"))