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:
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
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:
The SQL code looks like this:
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!
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!