Using DSum to calculate YTD

[cms2012]

Hey everyone,

I am trying to calculate YTD in a form using the selection made from a combobox . When I select Year, Month, and Sales Person from three comboboxes it auto populates text boxes. All this works, the problem is I cannot figure out how to get the year to date for the year that I choose from. I have been trying to use the DSum function to figure this out but it is not going to well. I have my date split up into metMonth (data is 01, 02, 03 etc) and metYear. Here is what I have for the VBA code:

Private Sub cmbsalesperson_AfterUpdate()
Me.txtwinstotal_month = Me.cmbsalesperson.Column(1)
Me.txtwins_month = Me.cmbsalesperson.Column(3)
Me.txtquotestotal_month = Me.cmbsalesperson.Column(2)
Me.txtquotes_month = Me.cmbsalesperson.Column(4)
Me.txtactualmonth = Me.cmbsalesperson.Column(5)

Dim holdmonth As String
Dim holdperson As String
Dim holdpyear As Long

holdpyear = CLng(Me.cmbYear) - 1
holdyear = CLng(Me.cmbYear)
holdmonth = Me.cmbMonth
holdperson = Me.cmbsalesperson

Me.txtwinstotalpym = DLookup("TOTALWINS", "METRICS", "SALES_PERSON_LAST_NAME='" & holdperson & "' AND clng(metMONTH)=" & holdmonth & " AND clng(metYEAR)=" & holdpyear)
Me.txtquotestotalpym = DLookup("TOTAL_QUOTES", "METRICS", "SALES_PERSON_LAST_NAME='" & holdperson & "' AND clng(metMONTH)=" & holdmonth & " AND clng(metYEAR)=" & holdpyear)
Me.txtwinspmonth = DLookup("WINSTOTALMONEY", "METRICS", "SALES_PERSON_LAST_NAME='" & holdperson & "' AND clng(metMONTH)=" & holdmonth & " AND clng(metYEAR)=" & holdpyear)
Me.txtquotespmonth = DLookup("QUOTESTOTALMONEY", "METRICS", "SALES_PERSON_LAST_NAME='" & holdperson & "' AND clng(metMONTH)=" & holdmonth & " AND clng(metYEAR)=" & holdpyear)
Me.txtplanmonth = DLookup("SALESPLAN", "SALESPLAN", "SALES_PERSON_LAST_NAME='" & holdperson & "' AND clng(SALES_YEAR)=" & holdyear)
Me.txtactualpym = DLookup("TOTAL_SALES", "[TOTAL SALES]", "EMPL_LAST_NAME='" & holdperson & "' AND clng(metMONTH)=" & holdmonth & " AND clng(metYEAR)=" & holdpyear)
Me.txtytdannual = DSum("YTDSALES", "YTD_SALES", "EMPL_LAST_NAME='" & holdperson & "' AND 'clng(metMonth) Between 01 and holdmonth' AND clng(metYear)=" & holdyear)
Me.txtplanannual = DLookup("SALESPLAN", "SALESPLAN", "SALES_PERSON_LAST_NAME='" & holdperson & "' AND clng(SALES_YEAR)=" & holdyear)

End Sub

The one in red is the only one that does not work. If snyone can help me I would be greatly appreciative.

Thanks

 

[pbaldy]

Try

DSum("YTDSALES", "YTD_SALES", "EMPL_LAST_NAME='" & holdperson & "' AND clng(metMonth) Between 1 and " & holdmonth & " AND clng(metYear)=" & holdyear)

 

[cms2012]

That worked great!! Thank you so much!!

 

[pbaldy]

Happy to help!