By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,335 Members | 2,313 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,335 IT Pros & Developers. It's quick & easy.

Summing data using SQL expressions in Visual Basic...

patjones
Expert 100+
P: 931
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

Expand|Select|Wrap|Line Numbers
  1. =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
Sep 18 '07 #1
Share this Question
Share on Google+
2 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Hi Pat

Something like this should work.

Me!txtLostDaysNoPay = DSum(DateDiff("d", [fldDateOfNoPayBegin], [fldDateReturned]), "tblWC")
Sep 19 '07 #2

patjones
Expert 100+
P: 931
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:

Expand|Select|Wrap|Line Numbers
  1. Me!txtLostDaysNoPay = DSum("DateDiff('d', [fldDateOfNoPayBegin], [fldDateReturned])", "tblWC")
Hi Pat

Something like this should work.

Me!txtLostDaysNoPay = DSum(DateDiff("d", [fldDateOfNoPayBegin], [fldDateReturned]), "tblWC")
Sep 19 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.