By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,291 Members | 3,096 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,291 IT Pros & Developers. It's quick & easy.

Getting Rid of an #ERROR returned if Field is null

P: 10
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
Share this Question
Share on Google+
7 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
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

Expert 100+
P: 1,221
Try
if Isnull(Datefield) then Valuefield = "" ...

Jim
Apr 6 '11 #3

Expert Mod 2.5K+
P: 2,545
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

P: 10
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

100+
P: 332
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

Expert 100+
P: 1,221
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

100+
P: 332
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

Post your reply

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