#Error

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

Guest

I have the following formula in the control source of one of my textboxes and
when there is no data in my "HoursAccrued" field then I get the #Error
displayed in my "DaysAccrued" textbox. How do I get rid of this error?

=[HoursAccrued]/8
 
Try this --
=IIF[HoursAccrued]=0,"Your message", [HoursAccrued]/8)
Or this --
=IIF[HoursAccrued] Is Null,"Your message", [HoursAccrued]/8)
 
Hi Karl,

Bad news...neither one of those worked. Would it matter that the
[HoursAccrued] has a control source that points to a function? Because that
field also has the same #Error in it even though the function has a Null
statement in it. See below...

Function VacHoursEarned(varYears As Variant) As Integer

If IsNull(varYears) Then
VacHoursEarned = Null
Exit Function
End If
Select Case varYears
Case 1
VacHoursEarned = 40
Case 2 To 4
VacHoursEarned = 80
Case 5 To 9
VacHoursEarned = 96
Case 10 To 14
VacHoursEarned = 120
Case 15 To 19
VacHoursEarned = 128
Case 20 To 24
VacHoursEarned = 136
Case 25 To 29
VacHoursEarned = 144
Case 30 To 34
VacHoursEarned = 152
Case Is >= 35
VacHoursEarned = 160
Case Else
VacHoursEarned = Null
End Select
End Function

KARL DEWEY said:
Try this --
=IIF[HoursAccrued]=0,"Your message", [HoursAccrued]/8)
Or this --
=IIF[HoursAccrued] Is Null,"Your message", [HoursAccrued]/8)

Secret Squirrel said:
I have the following formula in the control source of one of my textboxes and
when there is no data in my "HoursAccrued" field then I get the #Error
displayed in my "DaysAccrued" textbox. How do I get rid of this error?

=[HoursAccrued]/8
Click to expand...
Click to expand...
 
You cannot set an integer data type to Null. Look at these first lines of
your function:

Function VacHoursEarned(varYears As Variant) As Integer

If IsNull(varYears) Then
VacHoursEarned = Null
Exit Function

If you want this function to potentially return a Null value, declare it as
a Variant type.

--

Ken Snell
<MS ACCESS MVP>

Secret Squirrel said:
Hi Karl,

Bad news...neither one of those worked. Would it matter that the
[HoursAccrued] has a control source that points to a function? Because
that
field also has the same #Error in it even though the function has a Null
statement in it. See below...

Function VacHoursEarned(varYears As Variant) As Integer

If IsNull(varYears) Then
VacHoursEarned = Null
Exit Function
End If
Select Case varYears
Case 1
VacHoursEarned = 40
Case 2 To 4
VacHoursEarned = 80
Case 5 To 9
VacHoursEarned = 96
Case 10 To 14
VacHoursEarned = 120
Case 15 To 19
VacHoursEarned = 128
Case 20 To 24
VacHoursEarned = 136
Case 25 To 29
VacHoursEarned = 144
Case 30 To 34
VacHoursEarned = 152
Case Is >= 35
VacHoursEarned = 160
Case Else
VacHoursEarned = Null
End Select
End Function

KARL DEWEY said:
Try this --
=IIF[HoursAccrued]=0,"Your message", [HoursAccrued]/8)
Or this --
=IIF[HoursAccrued] Is Null,"Your message", [HoursAccrued]/8)

Secret Squirrel said:
I have the following formula in the control source of one of my
textboxes and
when there is no data in my "HoursAccrued" field then I get the #Error
displayed in my "DaysAccrued" textbox. How do I get rid of this error?

=[HoursAccrued]/8
Click to expand...
Click to expand...
Click to expand...
 
That worked perfectly Ken! It removed all my #Error messages. Thanks for your
help!

SS

Ken Snell (MVP) said:
You cannot set an integer data type to Null. Look at these first lines of
your function:

Function VacHoursEarned(varYears As Variant) As Integer

If IsNull(varYears) Then
VacHoursEarned = Null
Exit Function

If you want this function to potentially return a Null value, declare it as
a Variant type.

--

Ken Snell
<MS ACCESS MVP>

Secret Squirrel said:
Hi Karl,

Bad news...neither one of those worked. Would it matter that the
[HoursAccrued] has a control source that points to a function? Because
that
field also has the same #Error in it even though the function has a Null
statement in it. See below...

Function VacHoursEarned(varYears As Variant) As Integer

If IsNull(varYears) Then
VacHoursEarned = Null
Exit Function
End If
Select Case varYears
Case 1
VacHoursEarned = 40
Case 2 To 4
VacHoursEarned = 80
Case 5 To 9
VacHoursEarned = 96
Case 10 To 14
VacHoursEarned = 120
Case 15 To 19
VacHoursEarned = 128
Case 20 To 24
VacHoursEarned = 136
Case 25 To 29
VacHoursEarned = 144
Case 30 To 34
VacHoursEarned = 152
Case Is >= 35
VacHoursEarned = 160
Case Else
VacHoursEarned = Null
End Select
End Function

KARL DEWEY said:
Try this --
=IIF[HoursAccrued]=0,"Your message", [HoursAccrued]/8)
Or this --
=IIF[HoursAccrued] Is Null,"Your message", [HoursAccrued]/8)

:

I have the following formula in the control source of one of my
textboxes and
when there is no data in my "HoursAccrued" field then I get the #Error
displayed in my "DaysAccrued" textbox. How do I get rid of this error?

=[HoursAccrued]/8
Click to expand...
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculating accruals 1
Query Question 1
How to subtract a number from a Mergefield? 0
mirror data from subform to main form 3
Calculated field in main form 4
Set Control source at runtime 2
Prefix data in a text box 1
Access #error in microsoft access 2

Back
Top