473,574 Members | 2,636 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 18645
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
1,275 Recognized Expert Top Contributor
if Isnull(Datefiel d) then Valuefield = "" ...

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

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
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
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
1,275 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
  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 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
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 the following error when attempting to create or open the Web project located at the following URL: 'http://localhost/WebApplication1'. 'HTTP/1.1 500...
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 .htm pages just fine. My aspx pages however are generated from master pages where the title tag is set dynamically at run-time. So, it seens like...
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 component is used for generating Images on browsers. The image is generated in btyes array and passed to this component for further processing.
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 lWebResponse = (HttpWebResponse) lWebRequest.GetResponse(); StreamReader lResponseStream = new...
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 this property or method'. <form name="myform" action="main.asp" method="post"> <div id="content"> <h2 id="pageName">Main Page</h2> <div...
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 inside the folder "menu_script". The following is the code in this file. var...
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 called purchase_register_details, In sub form i have a unbound textfield called txt_Total_Amount where in i am calculating total amount for different...
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 decimal(2,2);declare prod_sp decimal(7,2);declare counts int;declare deal_check char(6);select deal_id into deal_check from db2admin.product where...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.