asked on 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
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
Microsoft Access
Last Comment
Scott McDaniel (EE MVE )
You have a number of ways.
One option would be:
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
ASKER
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
example-1.zip
screen-shot.xlsx
ASKER
Could I please get help with runtime error. see example-1 database. thanks
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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