Dcount in field on form (1 Viewer)

AndreG1

New member
Local time
Today, 13:01
Joined
Oct 8, 2017
Messages
6
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:

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]));
 

Ranman256

Well-known member
Local time
Today, 07:01
Joined
Apr 9, 2015
Messages
4,339
the 3rd param of Dcount is the filter, so if you only want 1 officer in query:"July2017Query", then:

count1 = DCount("officer1Lookup", "July2017Query","[officerID]=" & txtBox)
 

MarkK

bit cruncher
Local time
Today, 04:01
Joined
Mar 17, 2004
Messages
8,178
The problem here is the table design. There should never be a list like...
Code:
Item1, Item2, Item3, ..., ItemN
...in a single row. The numbered items are in a one-to-many relationship with the parent row (one parent row, many items), and should therefore be in a related table.
hth
Mark
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:01
Joined
May 7, 2009
Messages
19,175
add your strCriteria, strCriteria1, strCriteria2
to your DCount, eg:

count1=DCount("*", "July2017Query", strCriteria)
count2=DCount("*", "July2017Query", strCriteria1)
count3=DCount("*", "July2017Query", strCriteria2)

Me.Text18 = count1 + count2 + count3

count4=DCount("*", "August2017Query", strCriteria)
count5=DCount("*", "August2017Query", strCriteria1)
count6=DCount("*", "August2017Query", strCriteria2)

Me.Text20 = count4 + count5 + count6

count7=DCount("*", "September2017Query", strCriteria)
count8=DCount("*", "September2017Query", strCriteria1)
count9=DCount("*", "September2017Query", strCriteria2)

Me.Text22 = count7 + count8 + count9
 

plog

Banishment Pending
Local time
Today, 06:01
Joined
May 11, 2011
Messages
11,613
I see a lot of issues with everything you have. First your post doesn't tell us where to look. You posted both VBA and SQL. Does the query produce correct results but the VBA isn't picking them up? Or is it broke from the word go in the query? You initially mention syntax error--which implies your VBA because of the simplicity of your query, but don't tell us what line is throwing it. Point us in the right direction by giving us all the information you have.

Second, your table is not normalized. When you start numerating field names (officer1lookup, officer2lookup, etc.) you've set up your tables improperly. Instead the data in those fields need to go into a new table with 1 officer per record, not per field. So instead of 3 fields for 3 officers, you would have 3 records in the new table.

Third, it seems very inefficient to write code against a query for a specific time frame. I mean what happens as the months/years progress? Your code is still hard coded to look at July2017 query. If you are working in code, might as well make a very generic query that groups by every month/year, then use logic in your code to reference just the recrods from that query you need. That way, your code doesn't need to be manually updated to find the correct month/year for use in the future. Instead of 9 DCounts, the final answer you need could be simplified to just 1 query and 3 Dcounts into it (1 for each form input area to populate).

Fourth, when you mix ANDs and ORs in a query, you need to be very careful about using parenthesis correctly. You need to seperate the ANDs and ORs so that all the logic you want to work together, workds together. This might be your issue. You didn't tell us if the query is returning correct results.

Fifth, you never use your strLinkCriteria variables. You load them, but they it never gets used anywhere.

Sixth, as they stand now, the only difference between the values in count4, count5 and count6 will be different is if the fields they are looking at are blank in different amounts. If every Officer1Lookup, every Officer2Lookup and every Officer3Lookup is populated in August2017Query, then count4, count5 and count6 will all be equal--even if there are 6 different unique names in Officer1Lookup, 12 different unique names in Officer2Lookup and only 1 unique name in Officer3lookup. Those Dcounts aren't applying any criteria to the OfficerLookup field, they are all counting how many non-blank records there are, regardless of actual value in them.
 

AndreG1

New member
Local time
Today, 13:01
Joined
Oct 8, 2017
Messages
6
add your strCriteria, strCriteria1, strCriteria2
to your DCount, eg:

count1=DCount("*", "July2017Query", strCriteria)
count2=DCount("*", "July2017Query", strCriteria1)
count3=DCount("*", "July2017Query", strCriteria2)

Me.Text18 = count1 + count2 + count3

count4=DCount("*", "August2017Query", strCriteria)
count5=DCount("*", "August2017Query", strCriteria1)
count6=DCount("*", "August2017Query", strCriteria2)

Me.Text20 = count4 + count5 + count6

count7=DCount("*", "September2017Query", strCriteria)
count8=DCount("*", "September2017Query", strCriteria1)
count9=DCount("*", "September2017Query", strCriteria2)

Me.Text22 = count7 + count8 + count9

Thx for the suggestion ArnelGp, but that open the Criteria error can of worms again, Data type mismatch error 3464. has to be something with the ""&'""''"" etc etc .
 

AndreG1

New member
Local time
Today, 13:01
Joined
Oct 8, 2017
Messages
6
I see a lot of issues with everything you have. First your post doesn't tell us where to look. You posted both VBA and SQL. Does the query produce correct results but the VBA isn't picking them up? Or is it broke from the word go in the query? You initially mention syntax error--which implies your VBA because of the simplicity of your query, but don't tell us what line is throwing it. Point us in the right direction by giving us all the information you have.

Second, your table is not normalized. When you start numerating field names (officer1lookup, officer2lookup, etc.) you've set up your tables improperly. Instead the data in those fields need to go into a new table with 1 officer per record, not per field. So instead of 3 fields for 3 officers, you would have 3 records in the new table.

Third, it seems very inefficient to write code against a query for a specific time frame. I mean what happens as the months/years progress? Your code is still hard coded to look at July2017 query. If you are working in code, might as well make a very generic query that groups by every month/year, then use logic in your code to reference just the recrods from that query you need. That way, your code doesn't need to be manually updated to find the correct month/year for use in the future. Instead of 9 DCounts, the final answer you need could be simplified to just 1 query and 3 Dcounts into it (1 for each form input area to populate).

Fourth, when you mix ANDs and ORs in a query, you need to be very careful about using parenthesis correctly. You need to seperate the ANDs and ORs so that all the logic you want to work together, workds together. This might be your issue. You didn't tell us if the query is returning correct results.

Fifth, you never use your strLinkCriteria variables. You load them, but they it never gets used anywhere.

Sixth, as they stand now, the only difference between the values in count4, count5 and count6 will be different is if the fields they are looking at are blank in different amounts. If every Officer1Lookup, every Officer2Lookup and every Officer3Lookup is populated in August2017Query, then count4, count5 and count6 will all be equal--even if there are 6 different unique names in Officer1Lookup, 12 different unique names in Officer2Lookup and only 1 unique name in Officer3lookup. Those Dcounts aren't applying any criteria to the OfficerLookup field, they are all counting how many non-blank records there are, regardless of actual value in them.

Thank You fro your reply. I have two options to use.
1. make a query, and get the results
2. run the vba from textbox and get the results

the main problem with the query, (the included sql) is that the query counts all the NOT NULL records. I am not sure how to specify the results only to be equal to the textbox in the form. The SQL part specifies the tbl and the criteria for the query.

The other option for the vba part, has the problem of the all the NOT NULL being counted, and , instead of a sum of the 3 counts, ie 1+2+3 = 6 it is displayed as 123.....

Please take into account, I have had no formal training or any training at all, and I have taught myself what I know from TROLLING through thousands of blogs and articles like many of site visitors.

Thank you Again
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:01
Joined
May 7, 2009
Messages
19,175
if officer is string:

strCriteria="Officer1Lookup=" & Chr(34) & [Officer] & Chr(34)
...
...

if numeric:

strCriteria="Officer1Lookup=" & [Officer]
...
...
 

AndreG1

New member
Local time
Today, 13:01
Joined
Oct 8, 2017
Messages
6
if officer is string:

Strcriteria="officer1lookup=" & chr(34) & [officer] & chr(34)
...
...

If numeric:

Strcriteria="officer1lookup=" & [officer]
...
...
im starting to hate vb

12-12-17.jpg :mad:
h t t p s ://od.lk/d/MzZfMTU3OTMwMjlf/TMPD_be.accdb
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:01
Joined
May 7, 2009
Messages
19,175
can you type the error i cant read it.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:01
Joined
May 7, 2009
Messages
19,175
i think it's a string, so you have to use the other variant:


StrCriteria="officer1lookup=" & chr(34) & [officer] & chr(34)
StrCriteria1="officer2lookup=" & chr(34) & [officer] & chr(34)
StrCriteria3="officer2lookup=" & chr(34) & [officer] & chr(34)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:01
Joined
May 7, 2009
Messages
19,175
your file is big. i have to trim it down.
 

Attachments

  • aaTMPD_Extract.db.zip
    42.9 KB · Views: 88

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:01
Joined
Feb 19, 2002
Messages
42,981
Andre, please step away from the keyboard and process what plog told you. You might also take off your spreadsheet hat and do some reading on normalization. You are creating a ton of unnecessary work for yourself. Once you understand how to normalize your tables and then how to use a parameter in a query, all the hard-coding will go away.
 

Users who are viewing this thread

Top Bottom