472,374 Members | 1,274 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,374 software developers and data experts.

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 18318
2,322 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,266 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,266 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

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

Similar topics

by: Rod | last post by:
I have been working with ASP.NET 1.1 for quite a while now. For some reason, opening some ASP.NET applications we wrote is producing the following error message: "The Web server reported...
by: ankur seth via DotNetMonster.com | last post by:
Getting error Null Reference exception when a procedure is called in vb.net ..Why does it occur and how to resolve it. --- ANkur -- Message posted via http://www.dotnetmonster.com
by: Mike Dee | last post by:
I'm new to index server and can't get any DocTitle value back from index server (it is returned as null) for all my aspx pages. It do get the values from inside the <title> tags from my static...
by: ritesh4uall | last post by:
Hi, I am getting an error ERROR 'name' is null or not an object i dont know what it means, i am kinda new to javascript Please Help Regards
by: maheshwari.sumit | last post by:
Hi, I am getting error Error: File /default/default.asp CreateObject Exception. The CreateObject of '(null)' caused exception C0000005, when i trying to create an object of component. This...
by: Archana | last post by:
Hi all, I am having application where i am downloading xml content using webrequest. my code is as below HttpWebRequest lWebRequest = (HttpWebRequest) WebRequest.Create(URL); HttpWebResponse...
by: vikas251074 | last post by:
I am getting error while entry in userid field. When user enter his user id, an event is fired immediately and user id is verified using AJAX method. But I am getting error 'Object doesn't support...
by: suganya | last post by:
Hi Some professionals already has developed the project using menu. In my company, they have given me task to clear the error in that. It is a script file named as "menubarAPI4.js" which is kept...
by: sumit kale | last post by:
Hi, Can somebody help me resolve my problem ? I am getting error when calculating total using unbound textfiled in subform. I have a main form called purchase_register_master and a subform...
by: mohit1286 | last post by:
my stored procedure in db2 is---> create procedure temp_bill(in UPC_cd character(6)) language sql begin declare prod_cd character(8);declare prod_desc varchar(30);declare discount...
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web server and have made sure to enable curl. I get a...
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
by: Johno34 | last post by:
I have this click event on my form. It speaks to a Datasheet Subform Private Sub Command260_Click() Dim r As DAO.Recordset Set r = Form_frmABCD.Form.RecordsetClone r.MoveFirst Do If...
by: ezappsrUS | last post by:
Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...
by: DizelArs | last post by:
Hi all) Faced with a problem, element.click() event doesn't work in Safari browser. Tried various tricks like emulating touch event through a function: let clickEvent = new Event('click', {...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.