how to make a specific date the year a variable???

[BillG]

I have in a query a complex formula that has -
"Iif(date=#1/1/2013#,true,false)"
and my output in my formula is correct.

But I need to make the year = a variable not a constant or omit the year entirely.



I tried Iif(date=#1/1/Year(Date)#,true,false) and it gives me an error invalid date format.

I tried Iif(date=#1/1#, true, false) omitting the year and it gives the error invalid date format.

I really don't even need the year. I just need a Iif statement if the date is 1/1 then do this.

But I can't figure out a simple solution, yet it seems pretty simple.

Anyone with an idea???

[pbaldy]

I would use the DateSerial() function to make the year a variable, or the Month() and Day() functions to ignore year.

[BillG]

Understood, but these formulas are long and complex and to use date serial or month day parameters is extremely adding to the length of the formulas which this scenario is in multiple formulas and queries which is why I was trying to keep it simple using #mm/dd/yyyy# or something similar.

[pbaldy]

Okay; good luck.

[orange]

Paul has given you good advice. Perhaps you could tell us in plain English WHAT exactly is the Purpose of the parameter query?
Please show your parameter query SQL.

[BillG]

the purpose is that my queries if date is 1/1 rollback to previous month's data for the query and reports. but if 1/1/2013 (new year) the query can't rollback to the previous month because it is also a previous year. So I need to qualify if 1/1/Year(Date) as a variable I can then make it rollback correctly.

[pbaldy]

If you want the previous month, which you didn't say before:

'First of previous month
DateSerial(Year(Date()), Month(Date()) - 1, 1)

'Last of previous month
DateSerial(Year(Date()), Month(Date()), 0)

[BillG]

I understand, but I need something only for 1/1/year.

previous month January 1st is also a previous Year si I have to specify for 1/1/Year to use month December and previous year.

All other 11 months first day, I have a working formula.

[pbaldy]

I guess I don't really understand the goal. What's the formula that works for the other 11?