Function test Values and Sum (1 Viewer)

[khwaja]

I have 4 fields which have certain dates in it. Each date (regardless of what date it is) signifies the rate of progress. So I have following rate:

Survey ordered: 60%
Layout Ordered: 30%
Signage ordered: 5%
Refrigeration ordered: 5%

I would like to sum the percentages depending on the dates in each field filled in.

I tried to create a formula in the query but not too sure how to combine the result. So I thought might as well seek some help in building a function instead. Following is my effort. This only gives me one whole number where it meeting all 4 conditions. But zero elsewhere.

Function Rate(varSpeDateOrd As Variant, VarPrimSecOrd As Variant, varSignOrderDate As Variant, varRefOrderDate As Variant) As Integer

Dim var1 As Variant
Dim var2 As Variant
Dim var3 As Variant
Dim var4 As Variant


If Not IsNull(varSpeDateOrd) Then
var1 = 0.6

ElseIf Not IsNull(VarPrimSecOrd) Then
var2 = 0.4

ElseIf Not IsNull(varSignOrderDate) Then
var3 = 0.05

ElseIf Not IsNull(varRefOrderDate) Then
var4 = 0.05

End If
Rate = CInt(var1 + var2 + var3 + var4)

End Function

 

[plog]

You mentioned having dates like 5 times, yet all the sample data you provided there's no dates. So, I don't fully understand what you are trying to achieve. However, I can still help you with the issue you posted.

You need to assign default values to all your variables. Otherwise, its possible they never get set then when you try to add them together it makes math impossible. If you set them all to 0 before you run all those If statements, when you add them together in the final line it will work no matter how many of the if statements resolve to True.

 

[Cronk]

Presumably the passed values are the dates from the table.

I agree with Plog, set default values for the var variables.

Using IfElse, only one or none of the var's will be assigned a value. Use separate If/endif statements for each of the var's if you want a sum, otherwise the maximum returned will be 0.6

 

[khwaja]

Yes, the passed values are from 4 fields in a table. I changed the if statements as suggested and looks like I have the result I need. However, the suggestion around setting default value to zero is something I need help. Is this set as part of the variable declaration or should I be declaring as under in the beginning?

Var1 = 0
Var2 = 2

and so on.

 

[plog]

In VBA you should declare then set. Also, let's make this a learning opportunity: Instead of 4 independent varialbes, use an array (https://msdn.microsoft.com/en-us/library/wak0wfyt.aspx). Then to declare and set them you need these 2 lines:

Dim RatePart As Variant
RatePart = Array(0,0,0,0)

The first part of your homework is to figure out how to set each element to the correct value, then create a loop to add them all up.

The second part of your homework is to not use 4 variables or an array at all, but just one variable.

 

[plog]

Ok, just read through your code again and your logic is off. Logically, your code can be reduced to this statement:

If varSpeDateOrd Is Null return 0, otherwise return 1

I have read Cronk's post and what I am saying is different from what he said (he is correct though). Even if you follow his advice (which you should) and change the If Elses to Ifs, the only line of code that you have written that matters is this one:

If Not IsNull(varSpeDateOrd) Then
var1 = 0.6

Every other If statement has no bearing on the result of that function. You really need to revisit what you are trying to accomplish because I am certain that code doesn't do what you intend for it to do--even after you implement the default values and change the If Elses to Ifs , its not going to work like you expect.

 

[khwaja]

Thanks. I have amended the code since last post and in the light of Cronk's suggestion. It seems to be working OK unless there is further improvement that can be made.

Function Rate(varSpeDateOrd As Variant, VarPrimSecOrd As Variant, varSignOrderDate As Variant, varRefOrderDate As Variant) As Double


Dim var1 As Variant
Dim var2 As Variant
Dim var3 As Variant
Dim var4 As Variant


If Not IsNull(varSpeDateOrd) Then
var1 = 0.6
End If

If Not IsNull(VarPrimSecOrd) Then
var2 = 0.3
End If

If Not IsNull(varSignOrderDate) Then
var3 = 0.05
End If

If Not IsNull(varRefOrderDate) Then
var4 = 0.05
End If

Rate = (var1 + var2 + var3 + var4)

End Function