Now how do I count the number of results i get in the report/query?

[gnibbles]

Ive tried dcount and count and all that crap but they keep asking me for more stuff. even with the dcount i put in criteria and it still looks for something. And the help feature on ms access is useless, sorry to anyone it may offend.

 

[EllieFant]

Thank you Philip, I will give this a try.

What I need to be able to do is print a report that tells me how many of these catnames were used during a certain period of time (using the Between [date:] and [date:] in a query). I need to know the count for all the catnames even if it is zero.

I will give your method a try and let you all know how it works out. Ellie
**Using Access 97**

lena.wood@wgint.com (work)
ellefant@bossig.com (home)

 

[EllieFant]

grrr to early in the morning.....

I want to make sure that if a user goes in on Monday and enters a report and uses one catname and on Tuesday changes their mind about what catname is being used, that it either changes the line entered on Monday or deletes the old line and makes a new one. I don't want to have the same record counted twice. Ellie
**Using Access 97**

lena.wood@wgint.com (work)
ellefant@bossig.com (home)

 

[PhilipSchroevers]

Ok, your SQL should look something like:

SELECT CAT_LOOKUP.CATEGORY, COUNT(Maintman.someField) AS NmbOfCats,<otherfields>
FROM Maintman RIGHT JOIN Cat_Lookup ON Maintman.category = Catlookup.category
WHERE (Maintman.Date >= Begindate and Mainman.Date <= Enddate) or Mainman.Date Is Null
GROUP BY Cat_lookup.category
ORDER BY Cat_lookup.category;

Begindate and Enddate represent you selection parameters of course.
The result NmbOfCats will be zero for categories not used in (your selection on) Maintman.
You can construct this code in the Query Design View by pressing the Totals button (the Sum sign). This will insert an extra line 'Total', set this field to Group by for Cat_lookup.category, and to Count for some field from Maintman (e.g. the keyfield).

 

[spiral123]

back to erikz's problem i am haveing the same problem and his solution works for me but i need to count a YES/No field rather than a text field, then it gives me a data type mismatch error. any suggestions?