Compare two Dates

[Alan]

I have a table that contains two date fields (DOB & DOD).

I need to add 21 years to DOB and add 7 years to DOD and then compare the
two results and place the most distant result (date) into the Destroy on
Field (located in the same table).

 

[fredg]

I have a table that contains two date fields (DOB & DOD).

I need to add 21 years to DOB and add 7 years to DOD and then compare the
two results and place the most distant result (date) into the Destroy on
Field (located in the same table).

You can determine that information by using an unbound control on a
form.
=IIf IsNull([DOD],"", IIf(DateAdd("yyyy",[DOB])+21 >
DateAdd("yyyy",[DOD])+7, DateAdd("yyyy",[DOB])+21,
DateAdd("yyyy",[DOD])+7))



As long as your table has the [DOB] and the [DOD] data stored, the
above calculation need not be stored in any table.
When you need the information, run the calculation, in a query, on a
report, or in a form.

 

[Alan]

Thanks!

Now where do I put so a long formula?

I have a table that contains two date fields (DOB & DOD).

I need to add 21 years to DOB and add 7 years to DOD and then compare the
two results and place the most distant result (date) into the Destroy on
Field (located in the same table).

You can determine that information by using an unbound control on a
form.
=IIf IsNull([DOD],"", IIf(DateAdd("yyyy",[DOB])+21 >
DateAdd("yyyy",[DOD])+7, DateAdd("yyyy",[DOB])+21,
DateAdd("yyyy",[DOD])+7))



As long as your table has the [DOB] and the [DOD] data stored, the
above calculation need not be stored in any table.
When you need the information, run the calculation, in a query, on a
report, or in a form.

 

[John Vinson]

I have a table that contains two date fields (DOB & DOD).

I need to add 21 years to DOB and add 7 years to DOD and then compare the
two results and place the most distant result (date) into the Destroy on
Field (located in the same table).

Update DestroyOn to:

IIF(DateAdd("yyyy", 21, [DOB]) > DateAdd("yyyy", 7, [DOD]),
DateAdd("yyyy", 7, [DOD]), DateAdd("yyyy", 21, [DOB]))

 

[fredg]

Thanks!

Now where do I put so a long formula?

I have a table that contains two date fields (DOB & DOD).

I need to add 21 years to DOB and add 7 years to DOD and then compare the
two results and place the most distant result (date) into the Destroy on
Field (located in the same table).

You can determine that information by using an unbound control on a
form.
=IIf IsNull([DOD],"", IIf(DateAdd("yyyy",[DOB])+21 >
DateAdd("yyyy",[DOD])+7, DateAdd("yyyy",[DOB])+21,
DateAdd("yyyy",[DOD])+7))



As long as your table has the [DOB] and the [DOD] data stored, the
above calculation need not be stored in any table.
When you need the information, run the calculation, in a query, on a
report, or in a form.

On a Form, or in a Report, write it as the the control source of an
unbound control.
=IIf IsNull([DOD],"", ...... etc.

In a query add a new column:
DestroyOn:IIf IsNull([DOD],"", ..... etc.

If you do use this in a query, and the query is the record source of a
report or form, then you can just use the [DestroyOn] field in the
report, without having to re-compute it.