473,882 Members | 1,566 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 18678
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,278 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,278 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 Internal Server Error'."
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 index server is just indexing my raw .aspx and .cs.aspx pages instead of actually running the page...
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 StreamReader(lWebResponse.GetResponseStream(),enc); Data = lResponseStream.ReadToEnd();
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 class="feature"> <h1>Surfing the intranet </h1> <p> This is a comprehensive information...
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 menuOffsetTop=menuOffsetLeft=submenuOffsetTop=submenuOffsetLeft=submenuOffsetRight=0; var Doc=this.document;var standards=Doc.createElement&&Doc.createTextNode?1:0;...
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 items purchased by giving following code in control source. ContolSource: =Sum() In my main form i...
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 db2admin.product.upc_cd=UPC_cd;if deal_check is not null then select prod_id,prod_desc,buy_discount,prod_sp...
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 usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
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 most users, this new feature is actually very convenient. If you want to control the update process,...
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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.