Counting

[IKinal]

Hi,

Let me start with some background of what I am trying to accomplish. Basically, I have a table that keeps track of votes for people in three columns, and the votes are given a category from 1 to 13. Now, there are two types of votes, with some of the numbers falling into one, others into another, and I am trying to get a count of these two types in a report by using a report module.

So I have my SQL query in my VBA module (using ADO) and it reads in the ID number of the person, but it keeps giving me a zero instead of the correct number. I then proceeded to cut and paste the SQL query it generated into a regular Access query, and it worked. Does anybody know if there is some sort of bug that would prevent a count query from working correctly in code, but not in a query? I can post the SQL statement if more information is needed.

Many thanks,
I. Kinal

 

[cmmrfrds]

Please post the query and the surrounding ADO code.

 

[IKinal]

Here it is:

Dim SQL As String
Dim ID As String
ID = Text0
SQL = "SELECT Count(*) AS CountOfRecords FROM KPZ INNER JOIN KPS ON KPZ.ID=KPS.ID WHERE (((KPZ.VOTE1) Like '01*' Or (KPZ.VOTE1) Like '02*' Or (KPZ.VOTE1) Like '03*' Or (KPZ.VOTE1) Like '04*' Or (KPZ.VOTE1) Like '05*' Or (KPZ.VOTE1) Like '06*' Or (KPZ.VOTE1) Like '10*' Or (KPZ.VOTE1) Like '11*' Or (KPZ.VOTE1) Like '12*' Or (KPZ.VOTE1) Like '13*') AND ((KPS.ID)=" & ID & &quot) OR (((KPS.ID)=" & ID & &quot AND ((KPZ.VOTE2) Like '01*' Or (KPZ.VOTE2) Like '02*' Or (KPZ.VOTE2) Like '03*' Or (KPZ.VOTE2) Like '04*' Or (KPZ.VOTE2) Like '05*' Or (KPZ.VOTE2) Like '06*' Or (KPZ.VOTE2) Like '10*' Or (KPZ.VOTE2) Like '11*' Or (KPZ.VOTE2) Like '12*' Or (KPZ.VOTE2) Like '13*')) OR (((KPS.ID)=" & ID & &quot AND ((KPZ.VOTE3) Like '01*' Or (KPZ.VOTE3) Like '02*' Or (KPZ.VOTE3) Like '03*' Or (KPZ.VOTE3) Like '04*' Or (KPZ.VOTE3) Like '05*' Or (KPZ.VOTE3) Like '06*' Or (KPZ.VOTE3) Like '10*' Or (KPZ.VOTE3) Like '11*' Or (KPZ.VOTE3) Like '12*' Or (KPZ.VOTE3) Like '13*'));"

Call Recordset.Open(SQL, Catalog.ActiveConnection, adOpenDynamic, adLockOptimistic)
Text4 = Recordset.Fields("CountOfRecords&quot.Value

Text0 is where the ID number occurs in the report, and Text4 is where the count is printed. KPS is the main table, and KPZ is where the votes are stored.

Thanks,
I. Kinal

 

[cmmrfrds]

The first thing that jumps out is the wildcard. ADO uses the % as the wildcard. The * will work if you use the Access query grid since that is going directly through Access, but through ADO you must use the %. There are some other differences which you can find by searching for differences between DAO and ADO.

 

[IKinal]

Thanks, that solved the problem!

I. Kinal

 

[IKinal]

OK, now another problem has come up with this query. It is only giving me a count of 1 when it finds a match in the first column, but it is not giving me a 2 or 3 if it finds additional matches in the second or third column (vote2 or vote3). My guess is that this has something to do with the logic of the query expression, but I haven't had any luck manipulating it. Any suggestions?

Thanks,
I. Kinal

 

[cmmrfrds]

Maybe you can shorten the select criteria list. What comes after the 01, 02, etc.. in the fields.

You are joining on the ID column so you shouldn't need both.
AND ((KPS.ID)=" & ID & &quot) OR (((KPS.ID)=" & ID & &quot

It is hard to wade through all the parens, can you lay it out so that it is easier to follow - maybe on separate lines and groups.

 

[IKinal]

OK, I did some more experimentation last night, and I came up with something that works - I created three counts, one for vote1, one for vote2, and one for vote3, and then added them up. That seems to work, and it seems to work even faster than the original count did. Not sure if this is the best solution, but it gets the job done. So basically as you suggested I shortened the criteria list by breaking it up. Thanks for the feedback!

I. Kinal

 

[cmmrfrds]

Well, if it gets the job done and runs faster that sounds like success.