IIF statement in a query

B

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.
 
J

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.
Click to expand...

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],
Click to expand...
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]
 
B

brian.reimer

Thank you, I appreciate it. That solution works great.
John said:
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.
Click to expand...

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],
Click to expand...
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]
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Incone & Expenses Statement 0
QBF & Query Malfunction 0
Date comparison in IIF statement in a query 1
Schedule Comparison Error - mismatch duration - "The duration you entered isn't supported for this field. type a duration in the standard format" 0
IIf statement in query criteria 3
IIf Query assistance 7
Trying to create a report that will list unmatched records by Ticket Primary Key 0
IIf Statement in Query Criteria 1

Top