SQL Query Code Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to do a count query on multiple check boxes that have been
checked. In a SUM query, the result is a negative number and I cannot used
that.

Ok, this SQL Statement works fine:

SELECT COUNT(Calls.[Vent Free]) AS Vent_Free
FROM Calls
WHERE (((Calls.Date) Between [Enter Start Date] And [Enter Ending Date]))
And Calls.[Vent Free]=True;

But when I try to add a second part to it I get a result of 0 for the result
of the two parts:

SELECT Count(Calls.[Vent Free]) AS [Vent Free], Count(Calls.Vented) AS Vented
FROM Calls
WHERE (((Calls.[Vent Free])=True) AND ((Calls.Vented)=True) AND
((Calls.Date) Between [Enter Start Date] And [Enter Ending Date]));
 
Your second option is finding records where both check boxes are True and
presumably there are none.

You simplest solution is to return to using SUM() and use the ABS() function
to make the result positive, c.f.

SELECT ABS(SUM([Vent Free])) AS Vent_Free, ABS(SUM([Vented])) AS Vented
FROM Calls
WHERE Calls.Date Between [Enter Start Date] And [Enter Ending Date]

Incidentally using 'Date' as a column name is liable to come back and bite you
sooner or later. Access allows it but gets confused at times as it is the
name of an Access function. It is best to avoid all reserved words when
naming this, use something such as CallDate instead.

HTH
John
 
Back
Top