What's wrong with this filter?

[jgelpi16]

My ultimate goal is to run code block #2....However, it will not produce any results currently (this is a criteria in a query). IF I run code block #1 the results display as desired....What is wrong with my IIF statement in code block #2? I have been staring at this thing for entirely too long. Any help is much appreciated!

Code block #1

Between CDate(DatePart("m",Date())-1 & "/1/" & DatePart("yyyy",Date())) And CDate(DatePart("m",Date())-1 & "/31/" & DatePart("yyyy",Date()))

Code block #2

IIf(DatePart("m",Date())=1,Between CDate("12/1/" & DatePart("yyyy",Date())-1) And CDate("12/31/" & DatePart("yyyy",Date())-1),Between CDate(DatePart("m",Date())-1 & "/1/" & DatePart("yyyy",Date())) And CDate(DatePart("m",Date())-1 & "/31/" & DatePart("yyyy",Date())))

[ajetrumpet]

Between CDate(DatePart("m",Date())-1 & "/1/" & 

DatePart("yyyy",Date())) And CDate(DatePart("m",Date())-1 & 

"/31/" & DatePart("yyyy",Date()))

Code block #2

IIf(DatePart("m",Date())=1,Between CDate("12/1/" & 

DatePart("yyyy",Date())-1) And CDate("12/31/" & 

DatePart("yyyy",Date())-1),Between CDate(DatePart("m",Date())-1 

& "/1/" & DatePart("yyyy",Date())) And CDate(DatePart("m",Date())-1 

& "/31/" & DatePart("yyyy",Date())))

The problem might be the fact that you can't have a 'between' statement inside of an IIF() statement. As a matter of fact, just looking at it, logically, it doesn't make sense, and probably would look invalid to the parser. I'm 99% sure that's it.

And as a side note, think about the args in an IIF(). They are:

• arg
• value if true
• value if false

a 'between' statement can't possibly be evaluated back to a value, hence the statement is invalid.

[jgelpi16]

I do understand that...I thought this would logically work because I'm using the "BETWEEN" statements as the true and false "values"...

IIf(DatePart("m",Date())=1,Between CDate("12/1/" & 

DatePart("yyyy",Date())-1) And CDate("12/31/" & 

DatePart("yyyy",Date())-1),Between CDate(DatePart("m",Date())-1 

& "/1/" & DatePart("yyyy",Date())) And CDate(DatePart("m",Date())-1 

& "/31/" & DatePart("yyyy",Date())))

[ajetrumpet]

No, try something like this instead:

BETWEEN IIf(DatePart("m",Date())=1, CDate("12/1/" & DatePart("yyyy",Date())-1),

CDate(DatePart("m",Date())-1 & "/1/" & DatePart("yyyy",Date())))

AND IIf(DatePart("m",Date())=1, CDate("12/31/" & DatePart("yyyy",Date())-1),

CDate(DatePart("m",Date())-1 & "/31/" & DatePart("yyyy",Date())))

That's exactly the same thing, just parsable by the program.

[jgelpi16]

Ok, will do. Thanks a lot.