sql count result in report control 1

[jimlee]

Hi, I have the following sql which works fine in a stored query but i'm not sure how to get the result into a report control (txt box)

SELECT Count(*) AS Result
FROM tblClassActionData INNER JOIN tblActiveClients ON tblClassActionData.ID = tblActiveClients.Identification
WHERE (((tblClassActionData.ClaimDate)<#4/28/2005#));

i've tried putting the sql into the control source for the text box and also tried running it in the vba window but keep getting a zero answer. i'm now stumped!

Any help much appreciated.





jimlad

 

[PHV]

Perhaps this ?
=DCount("*", "tblClassActionData", "ClaimDate<#2005-04-28#")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jimlee]

Thanks for your response, I didn't really explain myself properly...

Basically there is a one to many relationship between tblClassActionData and tblActiveClients. For every record on tblClassActionData where ClaimDate<#2005-04-28# I need to count all the associated entries on the tblActiveClients table.

So if I have 5 records on tblClassActionData (where ClaimDate<#2005-04-28#) and they each have 5 associated entries on tblActive Clients, I would expect the answer to be 25.

Cheers

jimlad

 

[PHV]

which works fine in a stored query
=DLookUp("Result", "[name of stored query]")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jimlee]

ah, simple as that eh, for some reason I always avoid stored queries like the plague! but hey, it works!

again, thanks for your help

jimlad