Full Version: Select Count query in form
UtterAccess Forums > Microsoft® Access > Access Forms
Tidepooler
Hi,

Is there a way to put VBA code in a form that counts records meeting specific criteria from more than one table and apply that count to a variable?

I want to count up the number of records that meet certain criteria in two different related tables (i.e., innerjoin). Obviously, because of the two tables, DCount won't work. The count value would be stored in a variable. I just can't figure out the syntax...

strqrystring = "SELECT COUNT tblSmallEquipGroup.SmallEquipGroupID FROM tblSmallEquipGroup, tblSupplies"
strqrystring = strqrystring & " WHERE tblSmallEquipGroup.ManufacturerID = " & intManufacturer
strqrystring = strqrystring & " AND tblSmallEquipGroup.Model = '" & strModel
strqrystring = strqrystring & " AND tblSupplies.ItemNameID = " & intItemNameID
strqrystring = strqrystring & " AND tblSupplies.DetailedDescID = " & intDetailID
strqrystring = strqrystring & " INNERJOIN tblSmallEquipGroup.SupplyID = tblSupply.SupplyID;"
intCount = strqrystring?
or
intCount = DoCmd.RunSQL strqrystring?
or...??

Thanks.
Edited by: Tidepooler on Fri Jun 16 16:48:58 EDT 2006.
fkegley
You would use the DCount on the query:
ntCount = DCount("[FieldName]', "[QueryName]")
Tidepooler
Hi Frank, and thanks for responding.
tried your method. This is the code I used:
intCount = DCount ("SmallEquipGroupID", strqrystring)
And I got the following error:
Runtime Error '3163' The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.
Any ideas?
fkegley
An Integer type can only hold a number up to about 65500+. Try changing the type of intCount to Long. Type Long can hold a number into the billions.
Tidepooler
Hi Frank, and thanks again.
o be honest, I don't have enough data in my database to have that many records. But, I changed the variable type to long anyway, just for grins. I got the same error. Could this have to do with the query string being too long???
Thanks...
jmcwk
Frank,
id you leave someting out? DCount(expr, domain, [criteria]) just curious
Tidepooler
Hi John,
mmm...good point there. The criteria part is optional though, as you indicated. I've tried this a number of different ways and now have it as follows:
DCount("SmallEquipGroupID", "tblSmallEquipGroup", strqrystring)
Now I'm getting a syntax error instead, and I think it has something to do with my inner join.
THere is what I've changed it to...can anyone help me out here?
strqrystring = "SELECT tblSmallEquipGroup.SmallEquipGroupID FROM tblSmallEquipGroup"
strqrystring = strqrystring & " INNER JOIN tblSupplies ON "
strqrystring = strqrystring & "tblSmallEquipGroup.ManufacturerID = " & intManufacturer
strqrystring = strqrystring & " AND tblSmallEquipGroup.Model = """ & strModel
strqrystring = strqrystring & """ AND tblSupplies.ItemNameID = " & intItemNameID
strqrystring = strqrystring & " AND tblSupplies.DetailedDescID = " & intDetailID
strqrystring = strqrystring & " AND tblSmallEquipGroup.SupplyID = tblSupplies.SupplyID;"
By the way, I'm following a fairly lame example from an SQL book I have, but it doesn't provide much detail for adding in additional criteria other than the joined field. Any help would be greatly appreciated.
Tidepooler
Aha! I figured out that I needed to create and save my query in Access, rather than try to write the SQL code into VBA. Then, I can run the DCount on the saved query.
Thanks much for your help!
fkegley
Glad you figured it out! You're welcome.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.