IIF statement in a query

[brian.reimer]

I need to run a report that queries for records from the 1st to the
14th of a month and then from the 15th to the end of the month. I've
created a form which asks the user for the period's beginning date, (ie
05/01/2006) and validates the entry to make sure its either the 1st or
the 15th of a month. I've created an event command which runs the
report based on a query. The query uses the input from the form to
query the table and return records. I've tried using the following
query and it doesn't pull any records at all. Can anyone see what i'm
missing?

IIf((DatePart("d",[Forms]![Bonus_Galbraith]![Funded_Begin])=1),
(Between ([Forms]![Bonus_Galbraith]![Funded_Begin]) And
DateAdd("d",13,([Forms]![Bonus_Galbraith]![Funded_Begin]))),
(Between ([Forms]![Bonus_Galbraith]![Funded_Begin]) And
((DateAdd("m",1,[Forms]![Bonus_Galbriath]![Funded_Begin])-15))))

Any input is greatly appreciated.

 

[John Vinson]

I need to run a report that queries for records from the 1st to the
14th of a month and then from the 15th to the end of the month. I've
created a form which asks the user for the period's beginning date, (ie
05/01/2006) and validates the entry to make sure its either the 1st or
the 15th of a month. I've created an event command which runs the
report based on a query. The query uses the input from the form to
query the table and return records. I've tried using the following
query and it doesn't pull any records at all. Can anyone see what i'm
missing?

IIf((DatePart("d",[Forms]![Bonus_Galbraith]![Funded_Begin])=1),
(Between ([Forms]![Bonus_Galbraith]![Funded_Begin]) And
DateAdd("d",13,([Forms]![Bonus_Galbraith]![Funded_Begin]))),
(Between ([Forms]![Bonus_Galbraith]![Funded_Begin]) And
((DateAdd("m",1,[Forms]![Bonus_Galbriath]![Funded_Begin])-15))))

Any input is greatly appreciated.

Rather than forcing the user to enter a valid begin date, why not just
let them enter any date they wish and automatically calculate the
appropriate half-month? E.g. a criterion

= DateSerial(Year([Forms]![Bonus_Galbraith]![Funded_Begin],

Month([Forms]![Bonus_Galbraith]![Funded_Begin]),
IIF(Day([Forms]![Bonus_Galbraith]![Funded_Begin]) > 15, 15, 1))
AND
< DateSerialYear([Forms]![Bonus_Galbraith]![Funded_Begin],
IIF(Day([Forms]![Bonus_Galbraith]![Funded_Begin]) > 15,
Month([Forms]![Bonus_Galbraith]![Funded_Begin]) + 1,
Month([Forms]![Bonus_Galbraith]![Funded_Begin])),
IIF(Day([Forms]![Bonus_Galbraith]![Funded_Begin]) > 15, 1, 15))


John W. Vinson[MVP]

 

[brian.reimer]

Thank you, I appreciate it. That solution works great.

I need to run a report that queries for records from the 1st to the
14th of a month and then from the 15th to the end of the month. I've
created a form which asks the user for the period's beginning date, (ie
05/01/2006) and validates the entry to make sure its either the 1st or
the 15th of a month. I've created an event command which runs the
report based on a query. The query uses the input from the form to
query the table and return records. I've tried using the following
query and it doesn't pull any records at all. Can anyone see what i'm
missing?

IIf((DatePart("d",[Forms]![Bonus_Galbraith]![Funded_Begin])=1),
(Between ([Forms]![Bonus_Galbraith]![Funded_Begin]) And
DateAdd("d",13,([Forms]![Bonus_Galbraith]![Funded_Begin]))),
(Between ([Forms]![Bonus_Galbraith]![Funded_Begin]) And
((DateAdd("m",1,[Forms]![Bonus_Galbriath]![Funded_Begin])-15))))

Any input is greatly appreciated.

Rather than forcing the user to enter a valid begin date, why not just
let them enter any date they wish and automatically calculate the
appropriate half-month? E.g. a criterion

= DateSerial(Year([Forms]![Bonus_Galbraith]![Funded_Begin],

Month([Forms]![Bonus_Galbraith]![Funded_Begin]),
IIF(Day([Forms]![Bonus_Galbraith]![Funded_Begin]) > 15, 15, 1))
AND
< DateSerialYear([Forms]![Bonus_Galbraith]![Funded_Begin],
IIF(Day([Forms]![Bonus_Galbraith]![Funded_Begin]) > 15,
Month([Forms]![Bonus_Galbraith]![Funded_Begin]) + 1,
Month([Forms]![Bonus_Galbraith]![Funded_Begin])),
IIF(Day([Forms]![Bonus_Galbraith]![Funded_Begin]) > 15, 1, 15))


John W. Vinson[MVP]