Creating a Function Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
Click to expand...

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.
 
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


fredg said:
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
Click to expand...

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.
Click to expand...
 
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".
Click to expand...

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.....
 
Works like a charm!
Thanks a bunch
--
EugeneH


fredg said:
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".
Click to expand...

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.....
Click to expand...
 
Back
Top