Subtracting two date fields, result in a third

[DiverGuy]

Searched the forum but didn't find anything specifically on point. Could sure use some help.

I have inherited a table called tblLog with over 5000 entries and growing. It has the following columns:

ID
IntakeDate
CompletedDate
DaysToProcess​

My predecessor never used the DaysToProcess column but now management would like that information. (Already updated the input form to automatically calculate the DaysToProcess and update the table on all future entries.) It would be too time consuming to click through all 5000 entries and do the manual calculation.

I would like to come up with a procedure in a module that will iterate through each entry in the table, subtract the CompletedDate from the IntakeDate and update the DaysToProcess field, then go on to the next entry. This pseudocode is more or less what I'm trying to do:

Private Sub FixThatIdiotsMistake()

Dim x as Integer

For x = 1 to EndOfTable

DaysToProcess.Value = CompletedDate.Value - IntakeDate.Value​

Next​

End Sub

Any suggestions or guidance would be greatly appreciated.

Thanks,

DiverGuy

 

[Moniker]

No need to iterate through anything. A simple update query will do it:

CurrentDb.Execute "UPDATE tblLog SET DaysToProcess = DateDiff("d",[IntakeDate],[CompletedDate]);"

You can just copy/paste that into the immediate window and press enter and it'll do it for you.

EDIT: Paul is right in that storing calculated fields goes against good DB design principles.

 

[pbaldy]

I wouldn't bother saving it, since you can simply calculate it on the fly when you need it. A basic rule of normalization is not to store values that can be calculated from other fields. If you insist, use an update query:

UPDATE TableName
SET DaysToProcess = CompletedDate - IntakeDate

 

[DiverGuy]

I wasn't interested in saving it. Just wanted it to run once to avoid having to manually update the pre-existing data.

I ended up using:
Public Sub test()
CurrentDb.Execute "Update tblLog " _
& "Set DaysToProcess = CompletedDate - IntakeDate"
End Sub

That did the trick nicely. Thanks guys!

DiverGuy

 

[pbaldy]

I meant I wouldn't save the value at all, as in I wouldn't have that field in the table.