Select Records Based On Date Formula using IIF 1

[Bennie47250]

Using Access version 7.0

I’m working on a query that will run 2 times a year and each time the query run’s it need to extract records for the prior full six months. Using a scheduling tool, I want to schedule the report to run on Jan 5th and July 5 of each year.

When the report runs on 1/5/06, it needs to extract records with an invoice date in the period of 7/1/2005 thru 12/31/2005.

When the query runs on 7/5/2006, it needs to extract records with an invoice date in the period of 1/1/2006 thru 6/30/2006.

I was hoping to use a formula like IIf ( Date () between DateSerial ( Year ( Date () ) , 7, 1) and DateSerial ( Year ( Date () ) , 7, 15) , between DateSerial ( Year ( Date () ) , 1, 1) AND DateSerial ( Year ( Date () ) , 06, 30) , 7/15/05) (7/15/05 was just for testing)

However this did not return any records.

So for testing I took another stab at the formula and used this today: IIf (Date () = #8/8/2005#, Between #1/1/2005# And #6/30/2005#, Between #1/1/2005# And #6/30/2005#)

This did not return any records ether.

If I just use “Between #1/1/2005# And #6/30/2005#” in the invoice date field it does return records

Would appreciate any help developing a formula to do this.

Thanks

 

[travisbrown]

That's not what you should use IIF for. IIF is a way to construct conditional data return, not filter recordsets, much like you would with CASE statements.

For example, if you wanted to output the string 'Active' for every instance of true in a binary field, and 'Inactive' if false or otherwise: IIF(yourfield = True, 'Active', 'Inactive')

What's wrong with using BETWEEN? This is generally the accepted way to do it. You could do a DATE() > #yourenddate AND DATE() < #yourstartdate#, but to no benefit.

 

[Bennie47250]

Well I’m using IIF as I don’t know any other way too do what I want to do.

For what I’m attempting to do, IIF seems like the proper way of doing it.

Do you have a suggestion on how to do it?

Again what I need to do is if the current date is 7/5/current year, then select records with an invoice date between 1/1/current year and 6/30/current year. Else if the current date is 1/5/current year, it selects records with an invoice date between 7/1/previous year and 12/31/previous year.

 

[gol4]

between dateadd("M",-6,date()-5) and date()-5

 

[Bennie47250]

gol4,

Thanks for the beautifully simple formula for this. Works like a charm except I had to change the first -5 to a -4