473,882 Members | 1,566 Online

# Getting Rid of an #ERROR returned if Field is null

10 New Member
Hi Guys,

I have a probably relatively simple problem to fix but cannot seem to work it out.

The Function I have is

Expand|Select|Wrap|Line Numbers
1. If DateField = Date -1 Then
2.   ValueField = "1"
3. End if
The function works perfectly, The problem is. If DateField is Null then the information returned in ValueField is #ERROR.. I'd like it to just return Null if theres nothing in it.

I've experimented with:

Expand|Select|Wrap|Line Numbers
1. If Datefield = Null Then
2.   Valuefield = ""
3. Else
4.   If Datefield = Date -1 Then
5.     ValueField = "1"
6.   End if
7. End if
But it still returns the #Error.

:(
Apr 6 '11 #1
7 18678
TheSmileyCoder
2,322 Recognized Expert Moderator Top Contributor
You can use
Expand|Select|Wrap|Line Numbers
1. IsNull(Variable)
to check for null.

You can't compare (= is a comparison operator) to null, since null is not a value, its is the absence of a value, the absence of information.
Apr 6 '11 #2
jimatqsi
1,278 Recognized Expert Top Contributor
Try
if Isnull(Datefiel d) then Valuefield = "" ...

Jim
Apr 6 '11 #3
Stewart Ross
2,545 Recognized Expert Moderator Specialist
Try

Expand|Select|Wrap|Line Numbers
1. IF IsNull(DateField) THEN
2.   ValueField = ""
3. ELSE
4.   IF DateField = Date -1 Then
5.     ValueField = "1"
6.   End if
7. End If
-Stewart

Aah, Jim got in first with the same suggestion!
Apr 6 '11 #4
Calvin Dent
10 New Member
Hey Again Guys,

This is the exact Code piece I'm using:

Expand|Select|Wrap|Line Numbers
1. Public Function JobAttYest(Comfield As String) As String
2. If IsNull(Comfield) Then
3. JobAttYest = ""
4. Else
5. If Comfield = Date - 1 Then
6. JobAttYest = "1"
7. End If
8. End If
9. End Function
Added the IsNull but I'm still suffering the same Error issue... Can't seem to work it out :S
Apr 6 '11 #5
Mariostg
332 Contributor
You declared Comfield as String. A String cannot be null. I don't think it is a good habit to have a field value null. You should set it a default value.
Apr 6 '11 #6
jimatqsi
1,278 Recognized Expert Top Contributor
I'm not sure the declaration is the problem since it works when a date value is actually in Comfield.

First and foremost you should add some error handling in your code. Before the first line add
Expand|Select|Wrap|Line Numbers
1. On error go to JobAttYest_err
then at the bottom, before "End Function" add this
Expand|Select|Wrap|Line Numbers
1. Exit function
2.
3. JobAttYest:
4. msgbox Err.number & space(2) & err.description
5. resume next
6.
Then at least you will see any errors that occur.

There is also the IsDate function. You could replace "IsNull" with "IsDate"; maybe there are some non-null values that are not dates, and those would not end well for you with "IsNull".

Jim
Apr 6 '11 #7
Mariostg
332 Contributor
I did not see Comfield was a date. Then it should be declared as Date. Which cannot be null neither.

So again, instead of handling a null date, it should have a default value. Something eye catching like #1 1 1400#.

Expand|Select|Wrap|Line Numbers
1. Public Function JobAttYest(Comfield As Date) As String
2. If Comfield = #1/1/1400# Then
3.     JobAttYest = "" 'Or maybe Set this to 0
4. ElseIf Comfield = Date - 1 Then
5.     JobAttYest = "1"
6. End If
7. End Function
8.
Apr 6 '11 #8