1. Registered User
Join Date
Jan 2010
Posts
7

Hi Everyone.

In my customer table, I have a expired date. This field would fall into 1 of 3 status.. null, < Today or > today. I'm trying to create a simple report with three lines:

Null Records = X
Expired Records = X
Current = X

Any ideals on the best way to do this?

Thanks,
Kevin

2. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
What is X? Is it a count of these?

3. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Assuming so:
Code:
```SELECT SUM(SWITCH([expired date] IS NULL, 1))  AS [Null Records], SUM(SWITCH([expired date] = "< Today", 1))  AS [Expired Records]
FROM daTable```

4. Registered User
Join Date
Jan 2010
Posts
7
Yes, X is the total number of records...

I'm still a little new to Access, so I have a report with 3 unbound boxes...

Where would I use the statement you provided.

Thanks,
Kevin

5. Registered User
Join Date
Feb 2004
Location
Chicago, IL
Posts
1,312
Pootle Flump's solution uses a query to add up all the three scenarios you have. The query would then be used as the report's Record Source.

I agree with Pootle's approach. You will need to do any calculations before it gets to the report in one or more queries.

6. Registered User
Join Date
Jan 2010
Posts
7
I edit the code to use my names and paste it in the SQL view, but when I run it I get a data mismatch.

7. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
This is a date column isn't it?
Code:
```SELECT SUM(SWITCH([expired date] IS NULL, 1))  AS [Null Records], SUM(SWITCH([expired date] < Date(), 1))  AS [Expired Records
FROM daTable```

8. Registered User
Join Date
Jan 2010
Posts
7
working... maybe I did something wrong.

thanks for the help.

Kevin

9. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
No, you did nothing wrong. I took your first post literally. If you read the code in my first answer you will see what I did.

#### Posting Permissions

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