Using between statements in iif statements in criteria

[pd84313]

Hi - I am having some difficulty using a between statement in iif statements

My separate iif statement and between statements work but when I join them they don't

SELECT Table1.date1, Table1.value, Table1.value2
FROM Table1
WHERE (((Table1.date1)=IIf(Date()=#5/9/2003#,(Table1.date1) Between #1/1/2003# And #6/30/2003#)));

Please help - is it just a matter of using quotes in the right spot?

 

[scriverb]

Please just state what you want the selection criteria to do for this query.




Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[pd84313]

The iif will check to see if the current date falls between a date range and if it does it will then return the records with a date between a date range - this is going to find out which quarter is the current quarter and return last quarters data.

Ps - I know that the current code doesn't represent this it was just used to remove as many variables as possible from the code.

I tried to use another conditional statement in the return value of the iif statement but this didn't work either - so I guess I am asking if the TRUE value of an iif statement can be another conditional statement or does it have to be a value that is correct for that table column? ie a date in a date column????

 

[scriverb]

This is the key to your problem:
this is going to find out which quarter is the current quarter and return last quarters data.

Use this as the WHERE statement for your query:
WHERE [Table1].[date1] Between Switch( InStr(1, ",1,2,3," ,CStr(Month(Date())))>0 ,DateSerial(Year(Date())-1,10,1), InStr(1, ",4,5,6," ,CStr(Month(Date())))>0 ,DateSerial(Year(Date()),1,1), InStr(1, ",7,8,9," ,CStr(Month(Date())))>0 ,DateSerial(Year(Date()),4,1), InStr(1, ",10,11,12," ,CStr(Month(Date())))>0 ,DateSerial(Year(Date()),7,1)) And Switch( InStr(1, ",1,2,3," ,CStr(Month(Date())))>0 ,DateSerial(Year(Date())-1,12,31), InStr(1, ",4,5,6," ,CStr(Month(Date())))>0 ,DateSerial(Year(Date()),3,31), InStr(1, ",7,8,9," ,CStr(Month(Date())))>0,DateSerial(Year(Date()),6,30), InStr(1, ",10,11,12," ,CStr(Month(Date())))>0 ,DateSerial(Year(Date()),9,30))



Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[xazmi]

WHERE ((T1.BOOKDATE=IIf(IsNull([forms]![find]![bookdate]),[t1].[bookdate]) Or T1.BOOKDATE Between [forms]![find]![bookdate] And [forms]![find]![bookdate2]))

 

[pd84313]

Thanks guys but I used - it is a bit longer and required three queries because of the length - hey thanks again but can anyone answer my question about using a statement as the return value for an iif statement when it is true ?

SELECT Table1.date1, Table1.value, Table1.value2
FROM Table1
WHERE (((Table1.date1) Between (IIf(Date() Between CDate("01/01/" & CStr(Year(Date()))) And CDate("31/03/" & CStr(Year(Date()))),CDate("01/10/" & CStr(Year(DateAdd("yyyy",-1,Date())))))) And (IIf(Date() Between CDate("01/01/" & CStr(Year(Date()))) And CDate("31/03/" & CStr(Year(Date()))),CDate("31/12/" & CStr(Year(DateAdd("yyyy",-1,Date())))))) Or (Table1.date1) Between (IIf(Date() Between CDate("01/04/" & CStr(Year(Date()))) And CDate("30/06/" & CStr(Year(Date()))),CDate("01/01/" & CStr(Year(Date()))))) And (IIf(Date() Between CDate("01/04/" & CStr(Year(Date()))) And CDate("30/06/" & CStr(Year(Date()))),CDate("31/03/" & CStr(Year(Date())))))));


SELECT Table1.date1, Table1.value, Table1.value2
FROM Table1
WHERE (((Table1.date1) Between (IIf(Date() Between CDate("01/07/" & CStr(Year(Date()))) And CDate("30/09/" & CStr(Year(Date()))),CDate("01/04/" & CStr(Year(Date()))))) And (IIf(Date() Between CDate("01/07/" & CStr(Year(Date()))) And CDate("30/09/" & CStr(Year(Date()))),CDate("30/06/" & CStr(Year(Date()))))) Or (Table1.date1) Between (IIf(Date() Between CDate("01/10/" & CStr(Year(Date()))) And CDate("31/12/" & CStr(Year(Date()))),CDate("01/07/" & CStr(Year(Date()))))) And (IIf(Date() Between CDate("01/10/" & CStr(Year(Date()))) And CDate("31/12/" & CStr(Year(Date()))),CDate("30/09/" & CStr(Year(Date())))))));


SELECT Query4.date1, Query4.value, Query4.value2
FROM Query4

UNION SELECT Query5.date1, Query5.value, Query5.value2
FROM Query5;