• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 232
  • Last Modified:

Need help with DAvg field

I have a form which has two text boxes (short date fields) on it.    One is named txtStart and the other txtEnd.  Also on the form is a 3rd text box.  I want this 3rd text box to populate with a calculated number value when the 2nd date field has been updated.
 
This 3rd text box, the calculated field, needs to be a DAvg calculation but the syntax has me stumped.
It needs to be something like:

MyTxtfield = DAvg (Sum of [Hours] from tblJobLabor) / (Sum of [LaborQuantity] from tblJobLabor where [LCCN] is between 15205 and 15211) where UpdateDate is between Me.txtStart and Me.txtEnd

For reference LCCN is a number field.  
Can someone help?
0
SteveL13
Asked:
SteveL13
  • 3
  • 2
1 Solution
 
mbizupCommented:
Try this:

Dim dblTotalSum as double
Dim dblSumWCriteria as double

dblTotalSum = DSum("Hours","tblJobLabor")
dblSumWCriteria= DSum("LaborQuantity","tblJobLabor","(LCCN BETWEEN 15205 and 15211) AND (UpdateDate BETWEEN #" & me.txtStart & "# AND #" & me.txtEnd & "#))

IF dblSumWCriteria = 0 then
   MyTextField = 0  '<-- Prevents divide by zero errors
Else
    MyTextField = dblTotalSum / dblSumWCriteria
End if
0
 
SteveL13Author Commented:
This line must have an issue...  (turns red in the VBA code window)

dblSumWCriteria = DSum("LaborQuantity","tblJobLabor","(LCCN BETWEEN 15205 and 15211) AND (UpdateDate BETWEEN #" & me.txtStart & "# AND #" & me.txtEnd & "#))

--Steve
0
 
mbizupCommented:
Sorry -  it was missing an end quote.

dblSumWCriteria = DSum("LaborQuantity", "tblJobLabor", "(LCCN BETWEEN 15205 and 15211) AND (UpdateDate BETWEEN #" & Me.txtStart & "# AND #" & Me.txtEnd & "#)")
0
 
SteveL13Author Commented:
Nice work!  Thanks.
0
 
mbizupCommented:
Steve,

Glad to help out.

You've had a couple of questions lately about Domain Aggregate Functions... this article by Jim Dettman about DLookup and the Domain Functions might be a good read for you:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_12-Dlookup-and-the-Domain-Functions.html

If you do find it helpful, please be sure to click the "Yes" voting link at the top of the article.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now