Dsiplay result of SQL statement in control?

[Darren \(at work\)]

Hi,
I have the following SQL statement:

---Begin Code-----------

'--build SQL string to find total number
'--of Production rejects
strSQL1 = "SELECT Count(*) AS ProdFaults"
strSQL1 = strSQL1 & " FROM Inspection "
strSQL1 = strSQL1 & "WHERE ([InspectDate] Between #"
strSQL1 = strSQL1 & Format(dDate1, "mm\/dd\/yyyy") & "# And #"
strSQL1 = strSQL1 & Format(dDate2, "dd\/mm\/yyyy") & "#) AND
([Prod_Rej]=True)"
strSQL1 = strSQL1 & " AND ([Partcode] = "
strSQL1 = strSQL1 & strPartCode & ");"

--End Code----------------

I belive this should return a number value such as 4.
I would like to display this value in a label, but the control does not sem
to support this.
I have tried to display the result in a listbox with the follwing code:

--Begin Code---------------

lstDisplay.RowSource = strSQL1
lstDisplay.Requery

--End Code-----------------

This does not give any errors but it also does not display anything at all
in the listbox.
I have tried the SQL statement in a Query, which works as intended. The code
used in the Query is:

--Begin Code----------------

SELECT Count(*) AS ProdFaults
FROM Inspection
WHERE (((Inspection.[InspectDate]) Between [Enter first date] And [Enter
second date]) And ((Inspection.Prod_Rej)=True))
GROUP BY Inspection.Partcode
HAVING (((Inspection.Partcode)=[Enter PartCode]));

--End Code------------------

Anyone have any tips on how to display the results of the first query in a
form control?

Many thanks for any and all help and advice.
Darren

 

[Wayne Morgan]

Try the DCount function. For more information about similar functions look
for Domain Aggregate Functions in the help file.