IiF problem

[Steve_Bache]

Help again please !

I'm trying to populate a "Quarterly" field ( as in yearly quarters , values 1 through 4 ) in a query. I'm using the following expression :-

Qtr: IIf(Month([RequestDate])=1,1,0) Or IIf(Month([RequestDate])=2,1,0) Or IIf(Month([RequestDate])=3,1,0) Or IIf(Month([RequestDate])=4,2,0) etc.

However, the result is coming back with -1 for all records.

Question : am I correct in using the IiF function for this and if so what is wrong or what is the correct way to achieve the result I'm looking for ?

Cheers

Steve

 

[wazz]

to get the quarter use:

DatePart("q",[RequestDate])

perhaps a Select Case with that function will get what you need.

 

[Steve_Bache]

Wazz,

Thanks for the response. I'm pretty new to access - what is the function / syntax of SELECT CASE and how would I apply this code in the expression ?
Sorry for being clueless on this !

Cheers

Steve

 

[wazz]

hi. sry, i didn't see that you had responded.

maybe you don't need Select at all. just put:

Qtr: DatePart("q",[RequestDate])

in a new query-column.

 

[raskew]

Hi -

There are many instances when a fiscal year does not start on 1 Jan. Try the following which allows the user to specify the FY start date.

Function fGetFYQtr(FYStart As Date, pDate As Date) As Integer
'To call: from debug (immediate window)
'? fGetFYQtr(#7/1/05#, date())
'returns: 4
   fGetFYQtr = DateDiff("m", FYStart, pDate) \ 3 + 1
End Function

HTH - Bob

 

[Steve_Bache]

Sorry for not responding !!

Wazz - have used your suggestion successfully !

raskew - I'll store this one away for future !

Thanks to you both for your suggestions !

Steve