Query problem

[Brian Campbell]

Thanks in advance for any help with this query. I'm getting syntax
errors and "too many arguments" errors.

What I'm trying to do is count the number of trucks only if the
TotalLoss is above $0.00:

Count(IIf(qryTr ucks.TotalLoss > 0),(qryTrucks.S erialNumber)) AS
CountOfTrucks,

[Jeremy Wallace]

Re: Query problem

SELECT COUNT(SerialNum ber ) AS CountOfTrucks \
FROM qryTrucks
WHERE TotalLoss > 0

Jeremy
--
Jeremy Wallace
Fund for the City of New York

http://metrix.fcny.org

[Brian Campbell]

Re: Query problem

Thanks, but I made an error in typing and describing this, it's part of
a larger query, still the "count" syntax is throwing me:

SELECT qryTrucks.TagNu mber, qryTrucks.Drive r,
Count(IIf(qryTr ucks.TotalLoss > 0),(qryTrucks.T agNumber)) AS
CountOfTrucks, Sum(qryTrucks.L ossAmt) AS SumOfLossAmt,
Sum(qryTrucks.I ndemCost) AS SumOfIndemCost, Sum(qryTrucks.T otalLoss) AS
SumOfTotalLoss
FROM qryTrucks
GROUP BY qryTrucks.TagNu mber, qryTrucks.Drive r
HAVING (((Sum(qryTruck s.LossAmt))>0) AND
((Sum(qryTrucks .TotalLoss))>0) );

[Brian Campbell]

Re: Query problem

What my data looks like when I run the Datasheet for qryTrucks:
TagNumber Driver TotalLoss
1651434 125 $167.96
1624445 125 $493.11
9423432 125 $0.00
1554367 507 $549.81
4854242 507 $0.00
1546544 507 $494.25
8472145 507 $544.49

my needed query needs to look like this in Datasheet:
Driver CountOfTrucks
125 2
507 3

[Bob Quintal]

Re: Query problem

"Brian Campbell" <campbellbrian2 001@yahoo.com> wrote in
news:1142611840 .761810.239690@ z34g2000cwc.goo glegroups.com:
[color=blue]
> Thanks, but I made an error in typing and describing this,
> it's part of a larger query, still the "count" syntax is
> throwing me:
>
> SELECT qryTrucks.TagNu mber, qryTrucks.Drive r,
> Count(IIf(qryTr ucks.TotalLoss > 0),(qryTrucks.T agNumber)) AS
> CountOfTrucks, Sum(qryTrucks.L ossAmt) AS SumOfLossAmt,
> Sum(qryTrucks.I ndemCost) AS SumOfIndemCost,
> Sum(qryTrucks.T otalLoss) AS SumOfTotalLoss
> FROM qryTrucks
> GROUP BY qryTrucks.TagNu mber, qryTrucks.Drive r
> HAVING (((Sum(qryTruck s.LossAmt))>0) AND
> ((Sum(qryTrucks .TotalLoss))>0) );
>[/color]
Brian, study the IIF() function in the help file.

IIf(qryTrucks.T otalLoss > 0),(qryTrucks.T agNumber)
is not the correct syntax for iif().

IIf(qryTrucks.T otalLoss > 0,qryTrucks.Tag Number)
is, assuming that the value for the false value is null.

you may find it faster (and easier to understand) to
SUM(iif(querytr ucks.totalloss >0,1,0)) AS CountOfTrucks

--
Bob Quintal

PA is y I've altered my email address.

[Brian Campbell]

Re: Query problem

Thanks, that makes sense, but with "SUM(iif(queryt rucks.totalloss[color=blue]
>0,1,0)) AS CountOfTrucks" I get the error message "Wrong number of arguments used in query expression".[/color]

[Bob Quintal]

Re: Query problem

"Brian Campbell" <campbellbrian2 001@yahoo.com> wrote in
news:1142860779 .235705.313000@ v46g2000cwv.goo glegroups.com:
[color=blue]
> Thanks, that makes sense, but with
> "SUM(iif(queryt rucks.totalloss >0,1,0))
> AS CountOfTrucks" I get the error message "Wrong[color=green]
>>number of arguments used in query expression".[/color]
>[/color]
I don't see why, perhaps posting the full SQL would help.


--
Bob Quintal

PA is y I've altered my email address.