Finding value if two others are null

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database (Access 97) for tracking students in a course. The course
length can be from 6 to 8 classes long, indicated by fields ClassDate1,
ClassDate2, etc. to ClassDate8. I want to indicate on a report the date the
course ended.

I assume I would make a text box to show that if ClassDate8 is null, report
ClassDate7, and if ClassDate7 is also null, report ClassDate6. I have no
idea how to do this. Any help out there? I am rather new at Access.

Thanks in advance!
 
You could normalize your tables by removing your ClassDateN fields and
creating a table like:

tblClassDates
=============
ClassID (link to your original table's primary key)
ClassDate (date of class)

tblClassDates would have one record per class per date. The class ends on

SELECT ClassID, Max(ClassDate) as DateEnd
FROM tblClass
GROUP BY ClassID;
 
Back
Top