Values not being recognised???

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

Guest

Hi there,

Many thanks to Klatuu and SusanV for helping me with my code. But....the
code is not running properly. I was wondering if there was any way that the
Fees field may not be recognised as this could be a range of values. The data
inputter would type in say, 30000 in the form Fees field (which is in the
Currency format) so I wonder if this could be the problem? I can't see any
other way that this code would not be working.

Thanks in advance.

Gillian

Private Sub PI_Premium_Click()

If Type_of_Member = "I" Then
Select Case fees
Case Is <= "£50,000"
Select Case Me.PI_Limit_of_Indemnity
Case "£250,000"
Me.PI_Premium = "£125.00"
Case "£500,000"
Me.PI_Premium = "£135.00"
Case "£1,000,000"
Me.PI_Premium = "£150.00"
End Select
Case "£50,001" To "£75,000"
Select Case Me.PI_Limit_of_Indemnity
Case "£250,000"
Me.PI_Premium = "£145.00"
Case "£500,000"
Me.PI_Premium = "£160.00"
Case "£1,000,000"
Me.PI_Premium = "£175.00"
End Select
Case Is > "£75,000"
X = MsgBox("This will need to be referred to Saturn")
Select Case Me.PI_Limit_of_Indemnity
Case "£250,000"
Me.PI_Premium = "0.00"
Case "£500,000"
Me.PI_Premium = "0.00"
Case "£1,000,000"
Me.PI_Premium = "0.00"
End Select
End Select
End If

If Type_of_Member = "C" Then
Select Case fees
Case Is <= "£50,000"
Select Case Me.PI_Limit_of_Indemnity
Case "£250,000"
Me.PI_Premium = "£125.00"
Case "£500,000"
Me.PI_Premium = "£135.00"
Case "£1,000,000"
Me.PI_Premium = "£150.00"
End Select
Case "£50,001" To "£100,000"
Select Case Me.PI_Limit_of_Indemnity
Case "£250,000"
Me.PI_Premium = "£165.00"
Case "£500,000"
Me.PI_Premium = "£180.00"
Case "£1,000,000"
Me.PI_Premium = "£200.00"
End Select
Case "£100,001" To "£250,000"
Select Case Me.PI_Limit_of_Indemnity
Case "£250,000"
Me.PI_Premium = "£250.00"
Case "£500,000"
Me.PI_Premium = "£270.00"
Case "£1,000,000"
Me.PI_Premium = "£300.00"
End Select
Case Is > "£250,000"
Select Case Me.PI_Limit_of_Indemnity
Case "£250,000"
X = MsgBox("This will need to be referred to Saturn")
Me.PI_Premium.Value = "0.00"
Case "£500,000"
X = MsgBox("This will need to be referred to Saturn")
Me.PI_Premium.Value = "0.00"
Case "£1,000,000"
X = MsgBox("This will need to be referred to Saturn")
Me.PI_Premium.Value = "0.00"
End Select
End Select
End If

End Sub
 
Gillian,

Enclosing the amounts in quotes in your code makes VBA treat them as
text strings, whereas the control's source field (Fees) is currency, so
you need a numeric value there. The same goes for PI_Premium, assuming
that's currency as well. Your code should look something like:

Private Sub PI_Premium_Click()

If Type_of_Member = "I" Then
Select Case fees
Case Is <= 50000
Select Case Me.PI_Limit_of_Indemnity
Case 250000
Me.PI_Premium = 125
Case 500000
etc.

Note: The pound symbol prefix and the thousand and decimal separators
are only part of the formatting implicit on a currency field (i.e. they
are only being displayed), they are not actually part of the stored value.

HTH,
Nikos
 
Hi Nikos,

Thanks for your reply - it is now working perfectly. You are a genius.

Many thanks again.
 
Glad to have helped. Nothing to do with genius, try "been there" instead!

Nikos
 
Back
Top