1. Registered User
Join Date
Apr 2004
Location
ny, ny
Posts
224

I run a query that gives me the following columns
workorderno, downtime, mttr, workstatus, deptgroup, datereceived

I need to display the following values in a report. How do I do it, could some please guide me through the steps and the code

dt = sum(downtime) where deptgroup = 1 and datereceived between sd and ed
pdt = sum(downtime) where deptgroup = 2
wo = count(workorderno) where status in (1,2) and deptgroup = 1

totaldt = dt + pdt

Thank you

2. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
Look into using iif() in your sum calculations. Eg:

=sum(iif([downtime] BETWEEN [sd] AND [ed], [downtime], 0))

etc.

3. Registered User
Join Date
Apr 2004
Location
ny, ny
Posts
224
dt = sum(downtime) where deptgroup = 1 and datereceived between sd and ed

so the above could be put in a text box in the report as

dt = sum(iif(downtime), deptgroup =1, datereceived between sd and ed)

I dont think the syntax is correct

4. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
Close:

dt = sum(iif(deptgroup =1, iif(datereceived between sd and ed, downtime, 0), 0))

if the deptgroup field is = 1, then evaluate the next statement: If daterecieved is between sd and ed, then we'll be summing the downtime field, if either condition fails, it will be counted as 0

5. Registered User
Join Date
Dec 2003
Location
Dallas, TX
Posts
1,004
Originally Posted by Teddy
Close:

dt = sum(iif(deptgroup =1, iif(datereceived between sd and ed, downtime, 0), 0))

if the deptgroup field is = 1, then evaluate the next statement: If daterecieved is between sd and ed, then we'll be summing the downtime field, if either condition fails, it will be counted as 0
Hey, that was very good Teddy...the way you gave an example, but then actually explained what it meant. That's the way I think others should be helped so that later on they know what they are actually doing.
hats off to ya...keep up the good work

Bud

6. Registered User
Join Date
Apr 2004
Location
ny, ny
Posts
224
yeah thanks a lot teddy
that worked great for me

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•