Dsiplay result of SQL statement in control?

  • Thread starter Thread starter Darren \(at work\)
  • Start date Start date
D

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
 
Try the DCount function. For more information about similar functions look
for Domain Aggregate Functions in the help file.
 
Back
Top