In Access 97can I combine three date fields into one field? 1

[Cowboysooner]

My query calculates due dates and must use three fields due to the large size of the formulas. Consolidating the formulas to one or two fields results in a "too complex" error message.

I would like to combine the results of the three fields into a single field. The formula output for each field is either a date or Null.

CDate([Field1]) correctly displays the date, but

CDate([Field1]) Or CDate([Field2]) does not work.

 

[raskew]

Here's a very simplistic, but working, approach using the switch() function.

Given three date fields, Field1, Field2 and Field3, and that one will contain a date and two will not:

SELECT tblDateFields.Field1, tblDateFields.Field2, tblDateFields.Field3, Switch(IsDate([Field1]),[Field1], IsDate([Field2]),[Field2], IsDate([Field3]), [Field3]) AS MyDate
FROM tblDateFields;

 

[Cowboysooner]

Works great. I was able to combine columns using IIF but your method is much easier. Thanks for your help.