Hi guys,
I am doing my bit to help local LEO's and designing a db for them. I am currently struggling with a small syntax issue, which is bringing me to a grinding halt.
I have a textbox on a form which needs to give me a DCOUNT based on another textbox. I used a query, and got some results, but they seem to be incorrect. I would like to use the vba code this, instead of the query, But if all else fails, even help on the query would be brilliant. Here is my vba code::banghead:
here is my sql for the query
I am doing my bit to help local LEO's and designing a db for them. I am currently struggling with a small syntax issue, which is bringing me to a grinding halt.
I have a textbox on a form which needs to give me a DCOUNT based on another textbox. I used a query, and got some results, but they seem to be incorrect. I would like to use the vba code this, instead of the query, But if all else fails, even help on the query would be brilliant. Here is my vba code::banghead:
Code:
Me.Text43.Value = Me.Combo1.Column(0)
Me.Text47.Value = Me.Combo1.Column(1)
Dim officer As String
Dim Date1 As Date
Dim officerpay As String
officer = Me.Text47.Value
officerpay = Me.Combo1.Column(0)
Dim stLinkCriteria As String
Dim stLinkCriteria2 As String
Dim stLinkCriteria3 As String
Dim count1 As Integer
Dim count2 As Integer
Dim count3 As Integer
Dim count4 As Integer
Dim count5 As Integer
Dim count6 As Integer
Dim count7 As Integer
Dim count8 As Integer
Dim count9 As Integer
Dim count10 As Integer
Dim count11 As Integer
Dim count12 As Integer
stLinkCriteria = "[officer1Lookup]=" & "'" & officer & "'"
stLinkCriteria2 = "[officer2Lookup]=" & "'" & officer & "'"
stLinkCriteria3 = "[officer3Lookup]=" & "'" & officer & "'"
count1 = DCount("officer1Lookup", "July2017Query")
count2 = DCount("officer2Lookup", "July2017Query")
count3 = DCount("officer3Lookup", "July2017Query")
Me.Text18.Value = count1 + count2 + count3
count4 = DCount("officer1Lookup", "August2017Query")
count5 = DCount("officer2Lookup", "August2017Query")
count6 = DCount("officer3Lookup", "August2017Query")
Me.Text20.Value = count4 + count5 + count6
count7 = DCount("officer1Lookup", "September2017Query")
count8 = DCount("officer2Lookup", "September2017Query")
count9 = DCount("officer3Lookup", "September2017Query")
Me.Text22.Value = count7 + count8 + count9
End Sub
here is my sql for the query
Code:
SELECT tblCost.DateTowed, tblCost.Officer1Lookup, tblCost.Officer2Lookup, tblCost.Officer3Lookup
FROM tblCost
WHERE (((tblCost.DateTowed)>#9/1/2017# And (tblCost.DateTowed)<#9/30/2017#) AND ((tblCost.Officer1Lookup)=[Forms]![frmOfficerStats]![Text43])) OR (((tblCost.DateTowed)>#9/1/2017# And (tblCost.DateTowed)<#9/30/2017#) AND ((tblCost.Officer2Lookup)=[Forms]![frmOfficerStats]![Text43])) OR (((tblCost.DateTowed)>#9/1/2017# And (tblCost.DateTowed)<#9/30/2017#) AND ((tblCost.Officer3Lookup)=[Forms]![frmOfficerStats]![Text43]));