469,271 Members | 1,743 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

Getting Rid of an #ERROR returned if Field is null

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 17217
2,321 Expert Mod 2GB
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
1,260 Expert 1GB
if Isnull(Datefield) then Valuefield = "" ...

Apr 6 '11 #3
Stewart Ross
2,545 Expert Mod 2GB

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

Aah, Jim got in first with the same suggestion!
Apr 6 '11 #4
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
332 100+
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
1,260 Expert 1GB
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
  3. JobAttYest: 
  4. msgbox Err.number & space(2) & err.description
  5. 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".

Apr 6 '11 #7
332 100+
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
Apr 6 '11 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by ankur seth via DotNetMonster.com | last post: by
reply views Thread by maheshwari.sumit | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.