How To Count Dates on Report?

[netchie]

Hi,



I created a query and one of the fields in it is for dates. I need to create a report that will only count how many entries have dates and it shouldn't count those with no/blank dates .

Is there a way to put a criteria in my query for date field? What would be the formula? Or is there a formula that I can put straight to my report that will only count the ones with dates?

Thanks so much!
netchie

[ketbdnetbp]

You may have to tweak this a little since I haven't used it in quite some time but, if I recall correctly, you should be able to use Not IsNull as a criteria in the query. In which case, add an unbound textbox to the group footer in the report who's control source is set to: =Count(*)

(Null Dates will not display, all detail records will be counted)

Or,

Add an unbound textbox to the group footer in the report who's control source is set to: =Count(Not IsNull([DateFieldName]))

(Null Dates will display but, will not be counted)

Hope this gets you started,

Jim

[netchie]

Hi ketbdnetbp,

Thanks for quick reply. The 2nd one works great but I just found out that I need to add more into it

What I need now is to count the "Subject" field if its:

1. StartDate - has a date in it
EndDate - blank date

and any Subjects that has

2. StartDate: has a date in it
EndDate :today's date-onwards

My query has formula in Field:

Expr1: IIf([StartDate]<>"" And [StartEnd]="",1,0) but it's not accepting it and that's only for #1 situation. How can I add the #2 and what formula?


Thanks for your help
netchie

[rpeare]

Expr2: iif((not isnull([StartDate]) AND isnull([EndDate])) OR(not isnull([startdate]) AND [EndDate] >= date()), 1, 0)

I may have the bracketing fouled up but if I have this is the formula you want you just have to work on the bracketing.

Then you'd just have to sum the field to get the total count

[netchie]

Hi rpeare,

I think I am giving you a wrong info.

It does not need to enter what Start Date I need to capture before I can get the total. As long as the Start Date is not blank and End Date is blank and/or with future dates then it should be counted automatically.

This is the total that I am getting for having Start Date and End Date is blank
http://img.photobucket.com/albums/v482/netchie/withStartDateandEndDateBlank.jpg


This is the total that I am getting for having Start Date and End Date has recent and future dates.

http://img.photobucket.com/albums/v482/netchie/withStartDateandEndDateRecentDatetoFuture.jpg

HELP!

[rpeare]

Perhaps nobody mentioned this, if you want to COUNT items in a list in Access you have to use a summation or aggregate query. Look for the SIGMA button on your toolbar, this will place an extra row in your query that says TOTAL

In that line you want your expr2 (the formula I gave you) to say SUM in that row, remove all the date fields unless searching them with a criteria, if you are make sure the TOTAL line says WHERE and not GROUP BY.

Remove Column C whatever that is, a student identification I would assume.

[netchie]

Like this?

http://img.photobucket.com/albums/v4...tDateIssue.jpg

[netchie]

I have another option where I created a report and show all the Subjects with the following conditions:

StartDate: with dates
EndDate: no dates

StartDate: with dates
EndDate: "today's date" and future dates (up to 12/31/2011)

So i'm thinking of having a total below the report but as always I'm not good in creating formulas. What I'm thinking is to put:

Total A: Total of Subjects with StartDate and Blank EndDate
Total B: Total of Subjects with StartDate and EndDate with "today's date" and future dates (up to 12/31/2011)".

Any help is highly appreciated.

Thanks,
netchie

[netchie]

Thanks ketbdnetbp and rpeare's replies. YOU GUYS ROCK!!

This is the one I used by the way (rpeare's).

Expr2: iif((not isnull([StartDate]) AND isnull([EndDate])) OR(not isnull([startdate]) AND [EndDate] >= date()), 1, 0)

Extra: My report is showing all data even the ones that don't have Start Date (blank) and the ones with EndDate (past dates). Any suggestion on how to fix this? Maybe something in my query?

Thanks and have a great weekend
netchie

[rpeare]

put a criteria in your start date of

Is not null

Put a criteria in your end date of

<= (less than or equal to) <whatever date you put in>
or
>= (greater than or equal to) <whatever date you put in>

I would point out though that if you do that it makes the previous counting field moot because you're limiting your entire dataset to the exact same set of records.

[netchie]

rpeare once again, thank you .

I am using:

Start Date: Is not null
End Date: >=Date()

If I have new questions I will just create a new topic and hope you'll not get tired of checking and helping me, if ever.

Thanks again to you and to all who took time checking and fixing my Access issues.

netchie

[netchie]

Hi Guys,

Sorry but last minute decision has been declared and your help is highly appreciated.

This is the formula I am using:

Expr2: iif((not isnull([StartDate]) AND isnull([EndDate])) OR(not isnull([startdate]) AND [EndDate] >= date()), 1, 0)

But now, another new rule added that if StartDate has not come yet, so let's say today is Aug 29 but if the Start Date is Aug 30-future date, then it wont be counted as 1. How can this be added to my existing formula above?

[rpeare]

Expr2: iif((not isnull([StartDate]) AND isnull([EndDate])) OR(not isnull([startdate]) AND [EndDate] >= date()) OR (date() < [StartDate]), 1, 0)

[netchie]

Hi rpeare,

I edited my reply...

It didn't work

It should be counted as 1 if:

Start Date: past date up to today's date
End Date: Blank

OR

Start Date: past date up to today's date
End Date: Today's date to future

Your updated formula counted everything as 1.

HELP!!!
desperate netchie

[rpeare]

Expr2: iif((not isnull([StartDate]) AND isnull([EndDate])) OR(not isnull([startdate]) AND [EndDate] >= date()) OR (date() < [StartDate]), 1, 0)

This formula says

If the startdate is null AND the end date is null
not isnull([StartDate]) AND isnull([EndDate])
OR
the start date is null AND the end date is greater than or equal to today
not isnull([startdate]) AND [EndDate] >= date()
OR
the start date is less than Today
date() < [StartDate]

if you need all three criteria but you want the start date to be AFTER today's date you just need to change the sign on the last OR statement
date() >= [startdate] (startdate greater than or equal to today's date)
date() > [startdate] (startdate greater than today's date)