Select Case function

simon03

Registered User.
Local time
Today, 04:38
Joined
Aug 13, 2014
Messages
40
Hello,

Background context: I have had another thread about calculating a recursive formula (monthly cumulative performance) and I am using a modified version of the solution provided here (post #23):

http://www.access-programmers.co.uk/forums/showpost.php?p=1374081&postcount=23


My problem now is about evaluating the result of that query in order to calculate quarterly and annual performances.

The query returns correctly:

1) NULL - when I don't have values in the given month
2) 0 - when one of the components of the performance formula is 0
3) value (positive or negative) which can include any positive or negative value

In order to calculate the quarterly values I need the performance of the last month in the quarter and the first month in the quarter. My monthly values are stored in columns so say for Quarter 1, I would need something like this:

Code:
[mar]/[jan]-1

As you might have thought, the problems come when I have NULL or 0 values. Let me give you a couple of examples.

[jan] is NULL
[feb] is a value
[mar] is a value

Than my formula would have been
Code:
[mar]/[feb]-1

Second case:

[jan] is 0
[feb] is 0
[mar] is 0

In this case I would like the formula return '-' because I want to be able to spot the case in which the quarterly performance is 0 because the initial values were 0 from the case in which the performance was actually 0 (i.e., say [jan] = 101 and [mar] = 101 then performance is 0 which is different from having [jan] and [mar] = 0 thus performance '-').

You can combine the three states (NULL, 0, value) with the three months and find many combinations (I have found 27 relevant ones).

I initially thought to use SWITCH in SQL to evaluate the 27 combinations but I found out that SWITCH would evaluate non more than 15 conditions. So I guess the solution should be use a function in VBA which does what I was about to do with SWITCH in SQL.

I have built a test function which evaluates the numerator of my formula using only two cases (CASE 1: all variables have a value <>0, and CASE 2: the first month is null, the second is 0 and the third a value). Here the code:

Code:
Function evaluate_s(Var1, Var2, Var3) As Double
    Select Case Var1
              Case Is <> 0
                     Select Case Var2
                               Case Is <> 0
                                    Select Case Var3
                                        Case Is <> 0
                                            value_f = Var3
                                    End Select
                    End Select
                    
              Case Is = Null
                     Select Case Var2
                               Case Is = 0
                                      Select Case Var3
                                            Case Is <> 0
                                                value_f = Var3
                                      End Select
                    End Select
                                                      

    End Select
final_value = value_f
End Function

The SQL code looks like this:
Code:
SELECT Query1.[IPD_Ref], Query1.[ryear], evaluate_s(Query1.[jan],Query1.[feb],Query1.[mar]) AS Q1
FROM Query1;

My main question here is what is wrong in the syntax of my function. Why is not correctly evaluating at least the two cases in my function? I always get 0 for all records.

There is also another more general question and regards whether you think that this is the best approach, I mean using VBA and SELECT CASE (or if I could solve it in SQL).

Thanks!
 
OK, I found the mistake, in the second last line I should have:
Code:
evaluate_s = value_f

Now my test function is working fine. I suppose my function should work fine after I have implemented the 27 combinations!

Still my 'general' question stands: is this the best approach?

Thanks,
S.
 
Hi,

Thanks for your answer. I read that post but I suppose that even if I use that approach to set up my monthly performance I still have the same problem to calculate the quarterly performance.

In few words, my general question is if you think that I should use a function with the select case statement or if there is a better way to do that :)
 
Yes, but all you do is re-run the function anytime you need it. Perhaps display a date when it was last refreshed globally.

You'll probably end up with endless Select Case statements. Use the same process you're using for the monthly calculations but this time use the DatePart() function to get the quarter part of the date field.
 
Hi,

I have been able to use tehNellie suggestion to workout the general case of my quarter updates. The problem is that I can't figure out how to deal the zeros. Here my code:

Code:
UPDATE mytable AS t1 

INNER JOIN mytable AS t2 


ON (t1.IPD_Ref = t2.IPD_Ref) AND (t1.Month = DATEADD("m",3,t2.Month)) 


SET t1.Quarter = IIF( t2.Ratio=0,  0, (t1.Ratio/t2.Ratio-1)*100);

The IIF condition is not really doing what it is supposed to do. In reality what it should do is:

IF t2.Ratio=0 Then Use the latest not Zero Value in the Quarter and calculate the right formula.

So Say:

Jan = 12
Feb = 14
Mar = 0

In my case it finds that Mar is 0 and SET the value in field Quarter = 0, while what it should do is to calculate (t1.Ratio/14-1)*100

I don't know how to approach this problem. I tried to use subqueries within the IFF statement without any success (not sure if they were wrong or if it is not the right approach).

Your help/suggestions are very much appreciated as I am lost!!! :(
 
Thanks for taking time to have a look at my problem!

I have uploaded a sample. I have selected some cases which are giving me big headaches !

In the attached file there are three cases: a, b, c ('name' field).

First case (a) has values between Jan-1992 and Aug-1993. The problem here is to calculate the value of the last Quarter (Q3_1993) because it is not complete but we want to account for it. So we use the last month value (ratio) and the value of the last month of the previous quarter.

Second case (b) has values between Oct-1995 and Dec-1997. The problem is that the first 14 months have value 0 (thus quarterly values 0) but the first quarter of 1997 should use a formula as it was the first month recorded for b.

Third case (c) has a lot of zeros but has values for two months which are in different quarters thus we must have values for both quarters Q1_1995 and Q2_1995.

I should stress that the large majority of cases have values between Jan and Dec of each year. Thus for those cases my code works well. Also I am calculating in a separate UPDATE object the value for the first quarter of the first year of record of each name.

The problem is for cases where record starts after Jan and/or when the field Ratio is 0.
 

Attachments

What I see is a spreadsheet with incomplete calculations and results but nothing with the data some of the results are based on. Plus you haven't uploaded a db showing what you're doing.
 

Users who are viewing this thread

Back
Top Bottom