ms access error handling

I keep getting a runtime error of 94 when I make the first select in the code below. How  do I create a error handle to capture null value or blank values.

Private Function calculateTotal(v1, v2)
    Dim iTier1 As Integer  ' number in control name for units sold controls - Tier1, Tier2, ...
    Dim iTier2 As Integer
    Dim cGSAR As Currency ' total per line
    Dim i As Integer 'counter for lines considered

THIS IS WHERE I BELIEVE I GET THE ERROR - I am making the first selection from combox.
 
    iTier1 = Right(v1, 1) ' gives 1 for US1, 2 for US2 and so on
    iTier2 = Right(v2, 1)
    cGSAR = 0
    For i = iTier1 To iTier2
        cGSAR = cGSAR + Me.Controls("Tier" & i)
    Next
   
    calculateTotal = cGSAR
End Function
deefelAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
You can't run CalculateTotal if either incoming value is NULL, so just check for that before you get started with the code:

Private Function calculateTotal(v1, v2)

    If Nz(v1, "") = "" Or Nz(v2, "") = "" Then
        Exit Function
    End If


    Dim iTier1 As Integer  ' number in control name for units sold controls - Tier1, Tier2, ...
    Dim iTier2 As Integer
    Dim cGSAR  As Currency    ' total per line
    Dim i      As Integer    'counter for lines considered


    iTier1 = Right(v1, 1)    ' gives 1 for US1, 2 for US2 and so on
    iTier2 = Right(v2, 1)
    cGSAR = 0
    For i = iTier1 To iTier2
        cGSAR = cGSAR + Me.Controls("Tier" & i)
    Next

    calculateTotal = cGSAR
End Function

Open in new window

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can use the Nz function:

If Nz(v1, "") <> "" Then
    iTier1 = Right(v1, 1) ' gives 1 for US1, 2 for US2 and so on
End If
If Nz(v2, "") <> "" Then
    iTier2 = Right(v2, 1)
End If
0
 
Lee W, MVPTechnology and Business Process AdvisorCommented:
You have a number of ways.

One option would be:
'First, set iTier1 to a default value - empty, 0, whatever 
iTier1 = "0" 'Or something else (including "") since it's otherwise not valid.

'Then test if v1 meets the criteria necessary to assign a different value to iTier1 - 
'leave the default if not, otherwise, make iTier1 the new value.
If IsNull(v1) = False Then
   If Len(v1) > 0 Then
      If IsNumeric(v1) = True Then
         iTier1 = Right(v1, 1) ' gives 1 for US1, 2 for US2 and so on
      End If
   End If
End If

Open in new window

0
 
deefelAuthor Commented:
I tried the code, but still got the error message ~ it could be I put the code in th wrong place.  I have attached a sample database and a screenshot.  When in the database select from the drop down for Tier1/PL 1a value ~ this is where the error happens
example-1.zip
screen-shot.xlsx
0
 
deefelAuthor Commented:
Could I please get help with runtime error.  see example-1 database.  thanks
0
All Courses

From novice to tech pro — start learning today.