DCOUNT / CASE ?

[Steven R via AccessMonster.com]

I have a table that has spending broken down by 5 dept numbers - it is
totalled at the purchase order level, with total spending by purchase order.

My boss wants to see a count, by dept, of the total # of purchase orders
where the total spend is :

0-5000
5,000 to 10,000
10,000 to 15,000

I tried grouping by dept, and in the group footer placing a DCOUNT function
in the control source of an unbound text box, but it doesn't work. I started
just trying to get a count of those under 5k, but even that didn't work:
=DCount("[pgrp]","DCbyPO","[sumofnetvalue<5000]")

The table name is "DCbyPO" the field name of the dept is "pGrp," and
"SumofNetValue" is the total spend for each PO

I also played around with writing a SELECT CASE function, but didn't know how
to implement it, or if that was the best way

Function setcategory(intspendcat As Integer)

Select Case intspendcat

Case Is < 5000
intspendcat = 1

Case 5000 To 10000
intspendcat = 2

Case 10000 To 15000
intspendcat = 3

Case 15000 To 20000
intspendcat = 4

Case 20000 To 24999
intspendcat = 4

Case Else
intspendcat = 0
End Select

End Function

 

[Duane Hookom]

This should work:
=DCount("[pgrp]","DCbyPO","[sumofnetvalue]<5000")

If DCbyPO contains only the PO summaries then you can use:
=Sum(Abs([sumofnetvalue]<5000))

 

[Steven R via AccessMonster.com]

Duane,
Thanks ! I think the ABS is what I needed...
Steve

This should work:
=DCount("[pgrp]","DCbyPO","[sumofnetvalue]<5000")

If DCbyPO contains only the PO summaries then you can use:
=Sum(Abs([sumofnetvalue]<5000))

I have a table that has spending broken down by 5 dept numbers - it is
totalled at the purchase order level, with total spending by purchase

[quoted text clipped - 45 lines]

End Function