IIF Statement Too Complex. Need other solution.

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

Guest

I have an Access 2003 form that contains a data entry field named OverAllGPA
and a Text Field named TotalPoints. Each student gets a predetermined amount
of points for the GPA they have. I am trying to get the points associated
with the Student's GPA to appear in the TotalPoints Text field on the form.
I have tried using an IIF statement
=IIf([OverAllGPA]=2.5,1,IIf([OverAllGPA]=2.6,2,IIf([OverAllGPA]=2.7,3,IIf([OverAllGPA]=2.8,4
etc. on this field but since I would have 16 IIFs in this one statement, I
get a message that says my expression is too complex. I decided to create a
table and enter all the GPAs and their related points and then just do a
lookup from the form to determine the amount of points they should recieve
but I cannot figure out the expression to use in the textfield.

Can anyone help?

The tblPoints Table has the following numeric fields:
GPA1
GPA1Points
GPA2
GPA2Points
etc.

Example data in this table would be:
2.5 (GPA1)
1 (GPA1Points)

2.6 (GPA2)
2 (GPA2Points)

Thanks.
 
Replace the IIf statement with =CalcTotalPoints([OverAllGPA])

Then put this function in whatever module you use for misc functions

Function CalcTotalPoints(lngGPA As Long) As Integer
'In the Line Above, make the data types match what is received and what is
passed
Select Case lngGPA
Case Is = 2.5
lngGPA = 1
Case Is = 2.6
lngGPA = 1
Case Is = 2.7
lngGPA = 1
Case Is = 2.8
lngGPA = 1
' etc.
End Select
End Function
 
There is a better way:

Where you have your IIF statment, replace it with a function. Put a
function in a module with a SELECT CASE Statment that returns the value:
Example: Replace IIF with =CalcTotalPoints(Me.OverAllGPa)
Then the function
Function CalcTotalPoints(lngGPA As Long) As Integer
'In the Line Above, make the data types match what is received and what is
passed
Select Case lngGPA
Case Is = 2.5
lngGPA = 1
Case Is = 2.6
lngGPA = 1
Case Is = 2.7
lngGPA = 1
Case Is = 2.8
lngGPA = 1
' etc.
End Select
End Function
 
Sorry for the multiple replies, Stonewall. I got a error trying to post, so
I reposted. Also, I notices that in my reply I was setting the return value
to 1 in every case. Obviously, you have figured out it should be the correct
point value for the GPA
 
Klatuu's example of using a user defined function will work. To use your
lookup table instead, try using the DLookup() function in your textbox.
 
Wayne,
That's good. When I wrote my response, he was looking for a replacement for
a really ugly IIF statement. He did say he was considering going with a
table. I saw in his later posts, he went that way. I would probably go with
the function.

Wayne Morgan said:
Klatuu's example of using a user defined function will work. To use your
lookup table instead, try using the DLookup() function in your textbox.

--
Wayne Morgan
MS Access MVP


Stonewall said:
I have an Access 2003 form that contains a data entry field named
OverAllGPA
and a Text Field named TotalPoints. Each student gets a predetermined
amount
of points for the GPA they have. I am trying to get the points associated
with the Student's GPA to appear in the TotalPoints Text field on the
form.
I have tried using an IIF statement
=IIf([OverAllGPA]=2.5,1,IIf([OverAllGPA]=2.6,2,IIf([OverAllGPA]=2.7,3,IIf([OverAllGPA]=2.8,4
etc. on this field but since I would have 16 IIFs in this one statement, I
get a message that says my expression is too complex. I decided to create
a
table and enter all the GPAs and their related points and then just do a
lookup from the form to determine the amount of points they should recieve
but I cannot figure out the expression to use in the textfield.

Can anyone help?

The tblPoints Table has the following numeric fields:
GPA1
GPA1Points
GPA2
GPA2Points
etc.

Example data in this table would be:
2.5 (GPA1)
1 (GPA1Points)

2.6 (GPA2)
2 (GPA2Points)

Thanks.
Click to expand...
Click to expand...
 
Back
Top