Counting specific dates

[kooley]

I am trying to count the number of records for the last five business days in one day increments on a report.

I have been playing around with a formula and this is how far I have gotten:

IIf([Registration Received]=Date()-1, Count([Registration Received]=1, 0)
This is for yesterday's records. Results should equal 3, but I keep getting 0.

 

[JasonPurdueEE]

Hey kooley,
You may try to use DCount. Search the Access help for "DCount Function" for a description/example.

JASON

 

[dhookom]

To "count" the records in your report where [Registration Received] is yesterday, use the expression:

=Abs( Sum( DateValue([Registration Received]) = Date()-1) )
If your dates do not include a time element then you don't need the DateValue().

 

[PaulBricker]

One other way to do it based on your original efforts is
=Sum(IIf([Registration Received]=Date()-1,1,0))

That should do it

Paul

 

[MichaelRed]

use a crosstab query with the piviot being the date, the aggregate being count() and criteria being the most recent "five days". But note tah this is (like many of the other suggestions) going to generate a number of "oddities" in the normal 'business' operation (dates where the business is closed).



Use 'ye olde' standby ({F1}) for specifics re crosstab.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[kooley]

Thank you for all your help! The "Sum" suggestion worked like a charm!

 

[kooley]

Thank you! The "Sum" suggesstion worked like a charm!