Question Testing for first and last day of this year or the previous year (1 Viewer)

[ghudson]

Can you tell why this is not working? I cannot get my IIF statement to work in the criteria of my query with the between dates I am using in my IIF. When I use the true or false parts by themselves, the criteria works in the query but I get no results when I use the IIF as listed below. This does test correctly to determine if the current month is January or not... IIf(Month(Date())=1,"TRUE","FALSE")

I am trying to build the date criteria for a query and I need to determine the first and last day of the current year and the first and last day of the previous year. Basically I have to use last years dates if the current month is January when the query is run or else use the current years dates if the query is run during any month other than January.

Anybody see what needs to be tweaked for my IIF in my query to use the correct dates between criteia? Did I goof and leave something out? Thanks!

IIf(Month(Date())=1,Between DateSerial(Year(Date())-1,1,1) And DateSerial(Year(Date()),Month(12),0),Between DateSerial(Year(Date()),1,1) And DateSerial(Year(Date())+1,Month(12),0))

I know the DateSerials I have constructed work for his my proof.

'1/1/2009
MsgBox "First day of previous year = " & DateSerial(Year(Date) - 1, 1, 1)
    
'12/31/2009
MsgBox "Last day of previous year = " & DateSerial(Year(Date), Month(12), 0)

'1/1/2010
MsgBox "First day of current year = " & DateSerial(Year(Date), 1, 1)

'12/31/2010
MsgBox "Last day of current year = " & DateSerial(Year(Date) + 1, Month(12), 0)

 

[ajetrumpet]

g,

i don't think access is going to read a BETWEEN statement in a BOOLEAN part of an expression. e.g.:

IIf(Month(Date())=1,

Between DateSerial(Year(Date())-1,1,1) And 
DateSerial(Year(Date()),Month(12),0),

Between DateSerial(Year(Date()),1,1) And DateSerial(Year(Date())+1,Month(12),0))

what you're essentially telling it here is that if the month IS january, the value to output is this: Between DateSerial(Year(Date())-1,1,1) And
DateSerial(Year(Date()),Month(12),0

I think it's reading that as a string, not a statement like you want. I know it's not reading it as a statement, because the return value of the IIF() is never a statement. always a data type. I'm guessing of course, but try this instead:

BETWEEN IIf(Month(Date())=1,

DateSerial(Year(Date())-1,1,1),
DateSerial(Year(Date()),1,1))

AND

IIf(Month(Date())=1,

DateSerial(Year(Date()),Month(12),0),
DateSerial(Year(Date())+1,Month(12),0))

 

[Brianwarnock]

I found this fascinating

"Last day of previous year = " & DateSerial(Year(Date), Month(12), 0)

I've always done it as Dateserial(Year(Date),1,0)
The use of the function Month alters the way Dateserial works.

BTW I agree with Adam's assessment of the problem regarding the IIF.


I would use
Where (Month(date())=1 And fldname Between etcetc) Or Month(Date()<>1 And fldname Between etcetc)


Brian

 

[ghudson]

I had never used Between before an IIF. Interesting fix. I will have to play with the Where suggestion to see how the differ yet provide the same result. Thanks Adam and Brian!!!!

Damn IIFs! ;-)