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?
SteveL13Asked:
Who is Participating?
 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.