how do you write a case statment in Microsoft Access SQL view

[Aboord]

Hi, top of the day to you,

I am trying to write a case statement in access from the sql view. It doesn't seem to work so i tried the switch syntax which returns an error.

Is it possible to use the case syntax in access sql view? if not how can i do this.

this is my sample query

SWITCH
(
(MONTH(SPELL_DISCHARGE_DATE) IN (4,5,6) AND YEAR(SPELL_DISCHARGE_DATE) =2007) = "Q1 07/08"
, (MONTH(SPELL_DISCHARGE_DATE) IN (7,8,9) AND YEAR(SPELL_DISCHARGE_DATE) =2007) = "Q2 07/08"
, (MONTH(SPELL_DISCHARGE_DATE) IN (10,11,12) AND YEAR(SPELL_DISCHARGE_DATE) =2007) = "Q3 07/08"
, (MONTH(SPELL_DISCHARGE_DATE) IN (1,2,3) AND YEAR(SPELL_DISCHARGE_DATE) =2008) = "Q4 07/08"
,FALSE, "Not Current Financial year"
) as Quarter​

Please help​

 

[namliam]

I think you are best of doing this in a function... because you will probably need this in more places than one.

But if you want to do this only in this query...
Something like this will be more flexible...
iif(Month(date) <= 3
, "Q4" & Year(date) -1 & "/" & Year(date)
, "Q" & int(((month(date)-1)/3)) & " " & Year(date) & "/" & Year(date) + 1
)

Good luck!

 

[Banana]

Aboord, have you tried Choose function?

Also, why isnt Switch working? Do you get an error or wrong results?


No, Jet doesnt have CASE statement; Switch() and Choose() are closest.

 

[Aboord]

Thanks Banana and mailman, thanks for the advice. i used a bit of both of your commenst. This was my first time using the switch syntax and i finally got a hang of it and got the querry to work. i then took it a step further by taking mailmans comments and creating a querry which pulls the dates into a table which i can call anyttime.

Regards
y'all

 

[raskew]

Hi -

Try modifying this to agree with your dates:

? "Q" & DateDiff("m", #4/1/07#, #1/1/08#) \ 3 + 1 & " " & format(#4/1/07#, "yy")& iif(datepart("m", #4/1/07#)>1, "/" & format(dateadd("yyyy", 1,#4/1/07#) , "yy"),"")

Result:
Q4 07/08

where #4/1/07# represents the FY start date
-- and --
#1/1/08# represents the date you are evaluating.

HTH - Bob

 

[namliam]

Try modifying this to agree with your dates:

And what is wrong with my solution in post #2?

 

[raskew]

Posted by Namliam
And what is wrong with my solution in post #2?

Nothing wrong with it but its date specific, written for a situation where the FY begins on #1-April-yyyy#.

I tried to provide something a little more versatile that allows the user to specify a FY start date.

Have a great day - Bob

 

[namliam]

Yes my suggestion does start from the logic that the FY starts on April 1...

 

[raskew]

Yes my suggestion does start from the logic that the FY starts on April 1...

Yeah, I understand, and the OP's original post shows that. Problem being, FYs can start on a variety of dates, depending on the whims of the user.

For example, the US Government's FY runs 1 Oct to 30 Sep. See here for a fair explanation: http://www.lectlaw.com/def/f043.htm

Bob