Help with Error Compiling Function 1

[penndro]

I could use some help with this function I am trying to make work in MS Access.

I need to test two conditions (total revenue) and (product line type) and assign an Account class based on the values in these two fields.

Here is how I wrote the funtion but when I put it in my query grid it says it can compile... Any help would truly be appreciated.

Function Class(TotRev As inter, prodLine As String)

Dim Class As String

If [TotRev] < 0 And [TotRev] > 49999 And [prodLine] = "International" Then
Class = "E"

If [TotRev] < 50000 And [TotRev] > 99999 And [prodLine] = "International" Then
Class = "D"

If [TotRev] < 100000 And [TotRev] > 249999 And [prodLine] = "International" Then
Class = "C"

If [TotRev] < 250000 And [TotRev] > 499999 And [prodLine] = "International" Then
Class = "B"

If [TotRev] < 500000 And [prodLine] = "International" Then
Class = "A"

End If
Class = Null
End Function

===========================
in my query grid I am calling the function this way;

Classification: class([net revenue],[product line name])

Pleae tell me where I am falling short here - this is actually my first function that I am writing - normally I copy and edit existing ones.

Thanks,
Penn

 

[PHV]

Anyway, get rid of this line:
Dim Class As String

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

Way too many errors.

I would not use Class as a function name Problem names and reserved words in Access.

You have 5 If with only 1 End If.

You have TotRev As inter and there is no "inter" datatype. I expect you need to use TotRev As Long

Dim Class As String creates an error since it is the name of your function.

What do you want to return if none of the conditions are met?
This function would always return Null because your last line is Class = Null. If you want Class to be a string, this will error.

None of your If will be true since your < and > seem to be reversed.

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

I forgot to mention the variable names should not be enclosed in []s.

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

I think this is what you are looking for:

Function GetClass(TotRev As Long, prodLine As String) As Variant

    Select Case True
        Case TotRev >= 0 And TotRev <= 49999 And prodLine = "International"
            GetClass = "E"
        Case TotRev >= 50000 And TotRev <= 99999 And prodLine = "International"
            GetClass = "D"
        Case TotRev >= 100000 And TotRev <= 249999 And prodLine = "International"
            GetClass = "C"
        Case TotRev >= 250000 And TotRev <= 499999 And prodLine = "International"
            GetClass = "B"
        Case TotRev >= 500000 And prodLine = "International"
            GetClass = "A"
        Case Else
            GetClass = Null
    End Select

End Function

I would actually try to model this using a table with MinRev, MaxRev, ProdLine, and Classification fields. You would create the table and store all the values of your ranges and the "International". When ranges change, you change the data rather than your code.

If you get the above function working as needed then save the table for another day.

Duane
Hook'D on Access
MS Access MVP

 

[penndro]

Thank you all for responding so quickly. Please understand this is my first attempt at writing a complete function so I have to get familiar with the syntax....

I looked at the code again and modified is as:

Function myClass(TotRev As Long, prodLine As String)
If [TotRev] < 0 And [TotRev] > 49999 And [prodLine] = "International" Then
myClass = "E"

If [TotRev] < 50000 And [TotRev] > 99999 And [prodLine] = "International" Then
myClass = "D"

If [TotRev] < 100000 And [TotRev] > 249999 And [prodLine] = "International" Then
myClass = "C"

If [TotRev] < 250000 And [TotRev] > 499999 And [prodLine] = "International" Then
myClass = "B"

If [TotRev] < 500000 And [prodLine] = "International" Then
myClass = "A"
Else
myClass = "X"
End If
End If
End If
End If
End If
End Function


==================

The function now runs but returns a null.

 

[PHV]

If [TotRev] < 0 And [TotRev] > 49999
NEVER true !

 

[penndro]

Duane,

That code works like a charm! Thank you so, so much for your assistance. I also feel more empowered with working with Access cause now I am using code in my database that I really was too scared to try before.

Appreciated,
Penn