count question

[ChipF]

I have a report that is grouped by cube type (MGR, SUP, PROD, etc), I want it to count how many cubes in each type are from each floor. The cube # looks like this 1-213 for first floor, 2-145 for second floor.

Here is how the report looks:

ADMIN (type) (I want a count by floor here)

Cube # Owner Type Occupied

Thanks if you can help at all.

 

[SoloDroid]

Hello ChipF,
Please check out thread703-477804 as it deals with a similar situation. Robert
•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•
Professional, affordable, Access database solutions and assistance

http://www.SoloDroid.com

°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°

 

[ChipF]

I tried this formula:

=Sum(IIf([cube #]="1",1,0))

but since a cube # looks like this 1-214, it just gives me zero, how do you tell it to look at just the first digit, I would want that formula to look at the first digit, see if it is a 1, and then count that for the floor

 

[ChipF]

thanks solodroid, I figured it out now, here is what I used

=Sum(IIf([cube #]like"1*",1,0)) for first floor total
=Sum(IIf([cube #]like"2*",1,0)) for second floor total