Creating a Function Problem

[Guest]

I am trying to create a function in MS Access (Module) that assigns a Grade
for a particular Credit Bureau Score but the functions I created returns the
same value for all scores. Must be doing something wrong. I'm new at this
so any help would be appreciated.

Here are two Functions I created, both returning the same value:

1st Function
Option Compare Database
Function Grade2() As Integer
If score >= 720 Then
Grade2 = 1
ElseIf score >= 680 And score < 720 Then
Grade2 = 2
ElseIf score >= 650 And score < 680 Then
Grade2 = 3
ElseIf score >= 630 And score < 650 Then
Grade2 = 4
ElseIf score >= 600 And score < 630 Then
Grade2 = 5
ElseIf score >= 0 And score < 600 Then
Grade2 = 6
End If
End Function


2nd Function
Option Compare Database
Function Grade1() As String
Grade1 = IIf(score < 600, "E", IIf(score < 630, "D", IIf(score < 650,
"C", IIf(score < 680, "B", "C"))))
End Function

 

[fredg]

I am trying to create a function in MS Access (Module) that assigns a Grade
for a particular Credit Bureau Score but the functions I created returns the
same value for all scores. Must be doing something wrong. I'm new at this
so any help would be appreciated.

Here are two Functions I created, both returning the same value:

1st Function
Option Compare Database
Function Grade2() As Integer
If score >= 720 Then
Grade2 = 1
ElseIf score >= 680 And score < 720 Then
Grade2 = 2
ElseIf score >= 650 And score < 680 Then
Grade2 = 3
ElseIf score >= 630 And score < 650 Then
Grade2 = 4
ElseIf score >= 600 And score < 630 Then
Grade2 = 5
ElseIf score >= 0 And score < 600 Then
Grade2 = 6
End If
End Function

2nd Function
Option Compare Database
Function Grade1() As String
Grade1 = IIf(score < 600, "E", IIf(score < 630, "D", IIf(score < 650,
"C", IIf(score < 680, "B", "C"))))
End Function

How does the function (eitehr one) know what the score value is?
Nowhere in either function are you passing the [Score] value.

Function Grade2(MyScore as Integer) As Integer
If MyScore >= 720 Then
Grade2 = 1
ElseIf MyScore >= 680 And MyScore < 720 Then
Grade2 = 2
etc.....
End Function

You would then pass the score to the function, iform a query:
NewScore:Grade2([ScoreFieldName])

Directly in a report or on a form:
=Grade2([ScoreFieldName])

Note: Look up the Select Case statement in VBA help. It often more
suitable than If then else.

Do the same for the other function as well.

 

[Guest]

Thanks for the response.

The value "Score" is a field in the query/form, so I am simply adding user
defined field in the query/form to utlize the function that I created.

For example: in the query in which the "Score" value exists I added another
user defined field as follows: Tier:Grade2()

Unfortunately, even though each record shows varying scores for each line
record the value "Tier" all equals "6". Therefore I know it is calling the
function to extract the value, but don't know why every single record is
resulting in a "6".




--
EugeneH

I am trying to create a function in MS Access (Module) that assigns a Grade
for a particular Credit Bureau Score but the functions I created returns the
same value for all scores. Must be doing something wrong. I'm new at this
so any help would be appreciated.

Here are two Functions I created, both returning the same value:

1st Function
Option Compare Database
Function Grade2() As Integer
If score >= 720 Then
Grade2 = 1
ElseIf score >= 680 And score < 720 Then
Grade2 = 2
ElseIf score >= 650 And score < 680 Then
Grade2 = 3
ElseIf score >= 630 And score < 650 Then
Grade2 = 4
ElseIf score >= 600 And score < 630 Then
Grade2 = 5
ElseIf score >= 0 And score < 600 Then
Grade2 = 6
End If
End Function

2nd Function
Option Compare Database
Function Grade1() As String
Grade1 = IIf(score < 600, "E", IIf(score < 630, "D", IIf(score < 650,
"C", IIf(score < 680, "B", "C"))))
End Function

How does the function (eitehr one) know what the score value is?
Nowhere in either function are you passing the [Score] value.

Function Grade2(MyScore as Integer) As Integer
If MyScore >= 720 Then
Grade2 = 1
ElseIf MyScore >= 680 And MyScore < 720 Then
Grade2 = 2
etc.....
End Function

You would then pass the score to the function, iform a query:
NewScore:Grade2([ScoreFieldName])

Directly in a report or on a form:
=Grade2([ScoreFieldName])

Note: Look up the Select Case statement in VBA help. It often more
suitable than If then else.

Do the same for the other function as well.

 

[fredg]

Thanks for the response.

The value "Score" is a field in the query/form, so I am simply adding user
defined field in the query/form to utlize the function that I created.

For example: in the query in which the "Score" value exists I added another
user defined field as follows: Tier:Grade2()

Unfortunately, even though each record shows varying scores for each line
record the value "Tier" all equals "6". Therefore I know it is calling the
function to extract the value, but don't know why every single record is
resulting in a "6".

Did you even attempt what I suggested?

Regarding Tier:Grade2()
You haven't passed the score value to the function, and the function
has no way of getting the score unless you name an argument to receive
it.

In the query:
Tier:Grade2([Score])

Make the function a Public Function by placing it in a Module.
Then, in the Function, write:

Function Grade2(MyScore as Integer) as Integer

Then, in the function body, use MyScore instead of Score.
If MyScore = etc.....

 

[Guest]

Works like a charm!
Thanks a bunch
--
EugeneH

Thanks for the response.

The value "Score" is a field in the query/form, so I am simply adding user
defined field in the query/form to utlize the function that I created.

For example: in the query in which the "Score" value exists I added another
user defined field as follows: Tier:Grade2()

Unfortunately, even though each record shows varying scores for each line
record the value "Tier" all equals "6". Therefore I know it is calling the
function to extract the value, but don't know why every single record is
resulting in a "6".

Did you even attempt what I suggested?

Regarding Tier:Grade2()
You haven't passed the score value to the function, and the function
has no way of getting the score unless you name an argument to receive
it.

In the query:
Tier:Grade2([Score])

Make the function a Public Function by placing it in a Module.
Then, in the Function, write:

Function Grade2(MyScore as Integer) as Integer

Then, in the function body, use MyScore instead of Score.
If MyScore = etc.....