creating an expression using more than 1 field in a table

  • Thread starter Thread starter Alton Barnes
  • Start date Start date
A

Alton Barnes

i have a table named 10Sep03air. in this table i have 8
date fields--seg1depdte thru seg8depdte--per record. the
dates are listed like 12/27/03 and as it is imported into
the database, it formats the date as "text". the 8 dates
in this table represent the travel dates of a passenger
flight itinerary. some of the fields are "null" depending
on the length of the itinerary.

seg1depdte is always the 1st outbound travel date. what
i'm looking for is the last date in the series (return
date). the last date may not always be seg8depdte because
some fields will be "null" depending on the passenger's
flight itinerary.

what i want is to build an expression that will look at
each record (or passenger) and tell me what is the end-
date or return date of the trip.

I tried different ways but i can't get it to work.
 
You would actually have to start with the last one and move forward until you find one
that isn't null.

=IIF(Not IsNull([seg8depdte]), [seg8depdte],
IIF(Not IsNull([seg7depdte]), [seg7depdte],
IIF(Not IsNull(seg6depdte]), [seg6depdte],
IIF(Not IsNull(seg5depdte]), [seg5depdte],
IIF(Not IsNull(seg4depdte]), [seg4depdte],
IIF(Not IsNull(seg3depdte]), [seg3depdte],
IIF(Not IsNull(seg2depdte]), [seg2depdte])))))))

This would all need to be on one line.

For shorter typing, you may also be able to use the following. However, I've never tried
this method before and so I don't know if it will work.

=Nz([seg8depdte], Nz([seg7depdte], Nz([seg6depdte],
Nz([seg5depdte], Nz([seg4depdte], Nz([seg3depdte],
Nz([seg2depdte], "")))))))

Again, it would all have to be on one line.
 
Back
Top