DateDiff 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.

 

[Michel Walsh]

Hi,




Just "do it", I mean add the IF-THEN code to what you already have (and
I assume whatever the code you actually have, it already "run" without
error, that you have the opwl.Edit statement, etc.) :


If Nz(opwl![U_DNA], opwl![U_Ref_Date])> opwl![U_Census_Date] Then
opwl![WAIT_DUR] =opwl![U_Ref_Date] -opwl![U_Census_date]
End If


It is generally preferable to Update the recordset once all the
individual fields modifications have been done.

opwl.Update


so the database engine can now check the validity of the record, as a
whole entity (checking the table rules and data integrity, if there are such
things).




Hoping it may help,
Vanderghast, Access MVP

 

[Beccy]

When you say 'just add it, you lose me.


The calculation:

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

when I add it I am doing the following:

opwl![WAIT_DUR] = DateDiff("d", Nz(opwl![U_DNA], opwl!
[U_Ref_Date]), opwl![U_Census_Date])
If Nz(opwl![U_DNA], opwl![U_Ref_Date])> opwl!
[U_Census_Date] Then
opwl![WAIT_DUR] =opwl![U_Ref_Date] -opwl![U_Census_date]
End If

Just adding it on the bottom, but it doesn't work!!!

-----Original Message-----
Hi,




Just "do it", I mean add the IF-THEN code to what you already have (and
I assume whatever the code you actually have, it already "run" without
error, that you have the opwl.Edit statement, etc.) :


If Nz(opwl![U_DNA], opwl![U_Ref_Date])> opwl! [U_Census_Date] Then
opwl![WAIT_DUR] =opwl![U_Ref_Date] -opwl! [U_Census_date]
End If


It is generally preferable to Update the recordset once all the
individual fields modifications have been done.

opwl.Update


so the database engine can now check the validity of the record, as a
whole entity (checking the table rules and data integrity, if there are such
things).




Hoping it may help,
Vanderghast, Access MVP

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.

.

 

[Beccy]

I got it now, it works now!!!

It is as follows:

opwl![WAIT_DUR] = DateDiff("ww", Nz(opwl![U_DNA], opwl!
[U_Ref_Date]), opwl![U_Census_Date])
If Nz(opwl![U_DNA]) > opwl![U_Census_Date] Then
opwl![WAIT_DUR] = opwl![U_Census_Date] - opwl![U_Ref_Date]
End If

-----Original Message-----
When you say 'just add it, you lose me.


The calculation:

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

when I add it I am doing the following:

opwl![WAIT_DUR] = DateDiff("d", Nz(opwl![U_DNA], opwl!
[U_Ref_Date]), opwl![U_Census_Date])
If Nz(opwl![U_DNA], opwl![U_Ref_Date])> opwl!
[U_Census_Date] Then
opwl![WAIT_DUR] =opwl![U_Ref_Date] -opwl![U_Census_date]
End If

Just adding it on the bottom, but it doesn't work!!!

-----Original Message-----
Hi,




Just "do it", I mean add the IF-THEN code to what

you
already have (and

I assume whatever the code you actually have, it already "run" without
error, that you have the opwl.Edit statement, etc.) :


If Nz(opwl![U_DNA], opwl![U_Ref_Date])> opwl! [U_Census_Date] Then
opwl![WAIT_DUR] =opwl![U_Ref_Date] -opwl! [U_Census_date]
End If


It is generally preferable to Update the recordset once all the
individual fields modifications have been done.

opwl.Update


so the database engine can now check the validity of the record, as a
whole entity (checking the table rules and data integrity, if there are such
things).




Hoping it may help,
Vanderghast, Access MVP

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.

.

.