Use If statement with AND operator in query

[Ac]

Hello,

I would like to write an If statement with the AND operator in a field in a
query.

For example: I have a total yearFee, and have to divide by 12 and fill them
in each monthFee (field). The starting month and ending month are varying for
each customer. If the total of months in current year from the starting month
to end of the year are less than 12, the remaining monthFees have to filled
in few months next year. And later on, I need to find out the Sum of each
monthFee (Sum of Jan, Sum of Feb, …) for all customers .

The statement I put in the query field is:
IIf( ([startingDate]<=(1/1/2007)) AND ((1/31/2007)<=[endingDate]),
[MonthFee],0)

The results are always 0, no matter I changed the date either inside or
outside of the startingDate and endingDate range.

What is wrong? Or do you have some better idea to handle this problem?

 

[Tom Lake]

Try this:

IIf([startingDate] <= #1/1/2007# AND #1/31/2007# <= [endingDate],
[MonthFee], 0)

Tom Lake

 

[Ac]

Thanks Tom! It works.

Ac