Only show field with date

[Secret Squirrel]

I have a table with 4 different date fields. Only 1 of the date fields can be
filled per record. I'm trying to set up a query where I can list the records
but instead of showing a null value for the 3 fields I just want to show the
field with the date. I tried this but this wasn't correct:

VDate: Not IsNull[RegularDate] Or Not IsNull[PayDate] Or Not
IsNull[AdvanceDate] Or Not IsNull[UnscheduledDate]

It's just giving me a "-1" value.

 

[pietlinden]

I have a table with 4 different date fields. Only 1 of the date fields can be
filled per record. I'm trying to set up a query where I can list the records
but instead of showing a null value for the 3 fields I just want to show the
field with the date. I tried this but this wasn't correct:

VDate: Not IsNull[RegularDate] Or Not IsNull[PayDate] Or Not
IsNull[AdvanceDate] Or Not IsNull[UnscheduledDate]

It's just giving me a "-1" value.

sounds like a dodgy design. How about having two columns [MyDate],
and [DateType].

Logically, it's the same thing, and much neater. And you can enforce
cardinality (1 and only 1) - if the field is required. Or 0 or 1 (not
required).

 

[Allen Browne]

Try something like this (all on one line):

VDate: IIf([RegularDate] Is Null, IIf([PayDate] Is Null,
IIf([AdvanceDate] Is Null, [UnscheduledDate], [AdvanceDate]),
[PayDate]),[RegularDate])

If only 1 date is allowed, it might be better to use 2 fields:
DateTypeID one of the 4 possible types.
TheDate the actual date value.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...