Nested IIf in Query

[wilson51]

My table includes 6 date fields (First, Second Third etc) which are used to trace advertised dates.

I need to be able to identify the latest advertised date from First, Second, Third etc and update that to a field LastDate

IIf([Openhouse]![two]>[Openhouse]![one],[Openhouse]![two],IIf([Openhouse]![three]>[Openhouse]![two],[Openhouse]![three],IIf([Openhouse]![four]>[Openhouse]![three],[Openhouse]![four],IIf([Openhouse]![five]>[Openhouse]![four],[Openhouse]![five],IIf([Openhouse]![six]>[Openhouse]![five],[Openhouse]![six],0)))))

Why won't it work?

 

[Joe4]

I hate to say it, but it sounds like you may have a design issue. It sounds like your tables may not be in First Normal Form (which says that the tables do should not contain repeating groups of fields).

Instead of having a table in which you have fields that says "1st instance", "2nd instance", etc, you should redesign it so that you have a table that has "instance number" as a field and "date of instance" as a field. So instead of having a row of data with six dates, you will have six row of data with one date.

Then, you can group the data in a query by the identifying field and use the MAX funxtion to return the most current date.

 

[wilson51]

I hate to say it, but it sounds like you may have a design issue. It sounds like your tables may not be in First Normal Form (which says that the tables do should not contain repeating groups of fields).

Instead of having a table in which you have fields that says "1st instance", "2nd instance", etc, you should redesign it so that you have a table that has "instance number" as a field and "date of instance" as a field. So instead of having a row of data with six dates, you will have six row of data with one date.

Then, you can group the data in a query by the identifying field and use the MAX funxtion to return the most current date.

Thanks for your help