Getting Rid of an #ERROR returned if Field is null

[Calvin Dent]

Hi Guys,

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

The Function I have is

If DateField = Date -1 Then 
  ValueField = "1" 
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:

If Datefield = Null Then
  Valuefield = ""
Else
  If Datefield = Date -1 Then 
    ValueField = "1" 
  End if
End if

But it still returns the #Error.

:(

[TheSmileyCoder]

You can use

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.

[jimatqsi]

Try
if Isnull(Datefield) then Valuefield = "" ...

Jim

[Stewart Ross]

Try

IF IsNull(DateField) THEN
  ValueField = ""
ELSE
  IF DateField = Date -1 Then 
    ValueField = "1" 
  End if
End If

-Stewart

Aah, Jim got in first with the same suggestion!

[Calvin Dent]

Hey Again Guys,

This is the exact Code piece I'm using:

Public Function JobAttYest(Comfield As String) As String
If IsNull(Comfield) Then
JobAttYest = ""
Else
If Comfield = Date - 1 Then
JobAttYest = "1"
End If
End If
End Function

Added the IsNull but I'm still suffering the same Error issue... Can't seem to work it out :S

[Mariostg]

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.

[jimatqsi]

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

On error go to JobAttYest_err

then at the bottom, before "End Function" add this

Exit function
 
JobAttYest: 
msgbox Err.number & space(2) & err.description
resume next
 

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

[Mariostg]

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#.

Public Function JobAttYest(Comfield As Date) As String
If Comfield = #1/1/1400# Then
    JobAttYest = "" 'Or maybe Set this to 0
ElseIf Comfield = Date - 1 Then
    JobAttYest = "1"
End If
End Function