date difference calculation

[Beccy Howard]

I have the following calculation within a module:


Dim wait As Database, wl As Recordset, prov As Recordset
Set wait = DBEngine.Workspaces(0).Databases(0)
Set opwl = wait.OpenRecordset("OP WL Test")

opwl![WAIT_DUR] = DateDiff("d", Nz(opwl![U_DNA], opwl!
[U_Ref_Date]), opwl![U_Census_Date])

This returns a wait duration in days. How can I add the
following criteria to the calculation? Where U_DNA is
greater than the U_Census_date then ignore U_DNA date and
calcuate wait duration U_Ref_Date - U_Census_date. If the
U_DNA is not higher than the U_Census_date then use the
formula above.

 

[John Vinson]

I have the following calculation within a module:


Dim wait As Database, wl As Recordset, prov As Recordset
Set wait = DBEngine.Workspaces(0).Databases(0)
Set opwl = wait.OpenRecordset("OP WL Test")

opwl![WAIT_DUR] = DateDiff("d", Nz(opwl![U_DNA], opwl!
[U_Ref_Date]), opwl![U_Census_Date])

This returns a wait duration in days. How can I add the
following criteria to the calculation? Where U_DNA is
greater than the U_Census_date then ignore U_DNA date and
calcuate wait duration U_Ref_Date - U_Census_date. If the
U_DNA is not higher than the U_Census_date then use the
formula above.

You'll need the IIF() function instead of NZ to do this (and I think
you'll need another line to open the recordset for editing too, if you
don't already in your actual code!) Try:

Dim wait As Database, wl As Recordset, prov As Recordset
Set wait = DBEngine.Workspaces(0).Databases(0)
Set opwl = wait.OpenRecordset("OP WL Test")

opwl.Edit
opwl![WAIT_DUR] = DateDiff("d",
IIF(opwl![U_DNA]<=opwl![U_Census_date], opwl![U_DNA],
opwl![U_Ref_Date]), opwl![U_Census_Date])

The IIF first argument will return NULL if U_DNA is NULL, and the
U_Ref_Date will be used.