Public Function Coding Problem

[hainz]

Hi Everyone,

First of all I am new to this forum and this is my first post so sorry if I am somehow doing this wrong.

I wonder if someone can help me. Bear in mind I have only just started writing code and am self teaching myself as I go along.

This is the problem.....
I am trying to write a Case Statement which I seem to have got the grasp of. However when I mention any other fields that are in my query it comes up with either error# or the result comes back as 0.

So I think my problem is with the way I am defining my fields that start with Allo_Nett_Comp.

The function should works as follows. If total_comp is the value of 4 it should find the minimum value that is found in the fields Allo_Nett_comp_ 1 , Allo_Nett_comp_2, Allo_Nett_comp_3 and Allo_Nett_comp_4.
All the Allo_nett_Comp fields are in a table called NEWPMS170Table and are Long integer.

P.s the Minimum function I refer to in my code works fine so that does not need to be amended.

Public Function AlloNettFunc(total_comp As Integer)

Dim allo_nett_comp_1 As Integer
Dim allo_nett_comp_2 As Long
Dim allo_nett_comp_3 As Long
Dim allo_nett_comp_4 As Long
Dim allo_nett_comp_5 As Long
Dim allo_nett_comp_6 As Long
Dim allo_nett_comp_7 As Long
Dim allo_nett_comp_8 As Long
Dim allo_nett_comp_9 As Long

Select Case [total_comp]

Case Is = 1
AlloNettFunc = [allo_nett_comp_1]

Case Is = 2
AlloNettFunc = Minimum([allo_nett_comp_1], [allo_nett_comp_2])

Case Is = 3
AlloNettFunc = Minimum([allo_nett_comp_1], [allo_nett_comp_2], [allo_nett_comp_3])

Case Is = 4
AlloNettFunc = Minimum([allo_nett_comp_1], [allo_nett_comp_2], [allo_nett_comp_3], [allo_nett_comp_4])

Case Is = 5
AlloNettFunc = Minimum([allo_nett_comp_1], [allo_nett_comp_2], [allo_nett_comp_3], [allo_nett_comp_4], [allo_nett_comp_5])

Case Is = 6
AlloNettFunc = Minimum([allo_nett_comp_1], [allo_nett_comp_2], [allo_nett_comp_3], [allo_nett_comp_4], [allo_nett_comp_5], [allo_nett_comp_6])

Case Is = 7
AlloNettFunc = Minimum([allo_nett_comp_1], [allo_nett_comp_2], [allo_nett_comp_3], [allo_nett_comp_4], [allo_nett_comp_5], [allo_nett_comp_6], [allo_nett_comp_7])

Case Is = 8
AlloNettFunc = Minimum([allo_nett_comp_1], [allo_nett_comp_2], [allo_nett_comp_3], [allo_nett_comp_4], [allo_nett_comp_5], [allo_nett_comp_6], [allo_nett_comp_7], [allo_nett_comp_8])

Case Is = 9
AlloNettFunc = Minimum([allo_nett_comp_1], [allo_nett_comp_2], [allo_nett_comp_3], [allo_nett_comp_4], [allo_nett_comp_5], [allo_nett_comp_6], [allo_nett_comp_7], [allo_nett_comp_8],[allo_nett_comp_9)


Case Else 'do nothing'

End Select
End Function

Any help is greatly appreciated.

Thanks

Danny

 

[DJkarl]

You say the data type in the table is a long integer, yet your argument is using an integer type and not a long. Change all your data types to longs and see how that works.

Edit:
in VBA integer is more like a short integer.