Summing data using SQL expressions in Visual Basic...

[patjones]

Hi:

I have two date fields, fldDateOfNoPayBegin and fldDateReturned in a table in my database. My goal is simple: I want to take the difference between the two for each record, and then sum that over all records. The result goes into a text box txtLostDaysNoPay on a form.

I've had success by setting the Control Source property for txtLostDaysNoPay (via the Properties box in Access) equal to

=Sum(DateDiff("d",[tblWC]![fldDateOfNoPayBegin],[tblWC]![fldDateReturned]))

My issue is that I want to be able to do this strictly in the VB module for the form. I've tried writing a few SELECT statements in VB to accomplish it, but then I can't run those statements because the DoCmd.RunSQL method will only run action queries.

Just want to keep everything in the VB module as much as possible. Thanks!

Pat

[MMcCarthy]

Hi Pat

Something like this should work.

Me!txtLostDaysNoPay = DSum(DateDiff("d", [fldDateOfNoPayBegin], [fldDateReturned]), "tblWC")

[patjones]

That works like a charm, and has clearly saved me a couple of hours of confusion at work today. Thanks so much!

I just have to note that the first argument in the DSum function is a string, so the DateDiff function needs to be enclosed in quotes like so:

Me!txtLostDaysNoPay = DSum("DateDiff('d', [fldDateOfNoPayBegin], [fldDateReturned])", "tblWC")

Hi Pat

Something like this should work.

Me!txtLostDaysNoPay = DSum(DateDiff("d", [fldDateOfNoPayBegin], [fldDateReturned]), "tblWC")