Formula to Count Dates (with conditions) (1 Viewer)

[netchie]

Hi,

I am new here and so desperate to ask anybody here to help me fix my database.

I need to create a report to count my Subjects if they only have the following date conditions:

#1
StartDate: with date
EndDate: blank

and any Subjects with

#2
StartDate: with date
EndDate: today's date or future date

I created a formula in my Query but i'm having errors:

Expr1: IIf([StartDate]<>"" And [StartEnd]="",1,0)

The above formula is just for #1 and it's already not working. I don't know how to create a formula for #2 or how to create formula for both

Please help!!!

Thanks,
netchie

P.S. I am using Access 2007

 

[pbaldy]

How about:

Expr1: IIf(IsDate([StartDate]) And IsNull([StartEnd]),1,0)

 

[netchie]

Hi pbaldy,

Thanks so much for your quick reply. You gave me a big smile as soon as I learned you posted.

It seems that it's giving me wrong total (820).

StartDate (with date) and EndDate (blank) should have a total of 136
StartDate (with date) and EndDate (recent dates to future dates) should have a total of 1699.

Thanks again for your help
netchie

 

[pbaldy]

Can you post the db here, or a representative sample?

 

[netchie]

Hi, pbaldy,

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.

it says I can't attach or link anythign since I haven't posted a lot yet
HELP!

 

[vapid2323]

You can upload .zip files

http://www.access-programmers.co.uk/forums/showthread.php?t=212124

 

[netchie]

Wow! You guys are the best taking care of a newbie *blush*

So anyways, as long as the StudyStart (with dates) and StudyEnd (blank or "todays" and future dates) are showing and with total.

Thanks,
netchie

 

[pbaldy]

Those are just pictures, so I can't test anything. Is this what you want?

IIf(IsDate([StartDate]) And (IsNull([StartEnd]) OR [StartEnd] >= Date()),1,0)

 

[netchie]

Still not working.

I have another option where I created a report and shows only 3 fields (Subjects, StartDate, and EndDate).

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

 

[pbaldy]

I know you have this elsewhere, so I'm not going to invest too much time in it. On a report, you can do this type of thing in the report footer:

=Sum(IIf(IsDate([StartDate]) And IsNull([StartEnd]),1,0) )

which will total the items that match the criteria.

 

[netchie]

Honestly no, I really thought of that - to sum the first condition, sum the second condition, and then total them up

By the way, you're super duper ROCK!!! It worked!!

P.S.

I will take advantage of your "smartness"; the report that I have is showing all data even those that don't have Start Date (blank). In Query, any suggestion on what to do so they won't show up on my report? Should I create a new topic for this instead?

Thanks a bunch. Seriously, YOU ROCK!!
netchie

 

[pbaldy]

Sounds like you want to add a criteria on that field of

Is Not Null

 

[netchie]

pbaldy 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

 

[pbaldy]

No problem netchie!