What's wrong with this formula??? (1 Viewer)

[mariaw]

Here it is:

=Count(IIf([Calendar Date]>=[First Day] and <=[Actual Last Day])

???
Maria

 

[MStef]

Hello Maria!

Everything is wrong.
What do you want to Count ????

 

[mariaw]

Formula

Hi

Sorry - I see what you mean; I want to count the number of days if they fall between the [First Day] and the [Actual Last Day]?

Maria

 

[MStef]

It is not enough, tell me how your table looks like.
Do you want to do COUNT in the query or in the form, and s.o.

 

[Pat Hartman]

count() counts ALL the ROWS of a domain. It doesn't count values. You can substitute Sum() for your purposes:

Sum(IIf([Calendar Date]>=[First Day] and [Calendar Date] <=[Actual Last Day]), 1, 0)

If the calendar date falls within the range, the IIf() will return 1. Otherwise it will return 0. You can then sum the value to "count" the dates in the range.

Keep in mind that conditions are not transitive. You always have to repeat the subject. You CANNOT say "If A = B or C". You MUST say "If A = B OR A = C". When you have a lot of values to compare A to, you can use the In() syntax - "If A In(B,C,D,E...)"