473,326 Members | 2,010 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

need a workaround for Nz function in MS Access

2
The following works when typed directly into access, but fails with an 'Undefined function Nz' when run through Java and ODBC:
select * from [info] where
CInt(Nz(ValX, 0)) <= 100 and CInt(Nz(ValY, 0)) > 0 and CInt(Nz(ValY,0)) >= 100;
(100 is just a sample value)
I tried the IsNull, but that doesn't work. Basically my problem is that the 2 columns ValX and ValY are set as text and can be blank. I need to test against an integer in my java program on non-blank entries.
I read that Nz is an internal function only so it can't be used from a program. Is there an alternative that I can use in Java?
Feb 2 '15 #1
5 1735
twinnyfo
3,653 Expert Mod 2GB
I do not think Java has an equivalent function, but you might be able to construct one easily.... Java is not really my bag, though.
Feb 2 '15 #2
Rabbit
12,516 Expert Mod 8TB
Can't you fix the data and data structure?

If not, you can try the IIF() function, COALESCE() function, or CASE control flow statement.
Feb 2 '15 #3
Seth Schrock
2,965 Expert 2GB
Both IsNull() and Nz() are access functions. The SQL version of IsNull() is Is Null and Is Not Null. That combined with the IIF() function that Rabbit mentioned should work.
Feb 2 '15 #4
jforbes
1,107 Expert 1GB
COALESCE() is pretty versatile especially if you are attempting to pull defaults from multiple places. ISNULL() tends to run faster in large queries. But as Rabbit mentions, if you can fix your data so that it defaults to 0 and wont allow Nulls, you will be better off.
Feb 2 '15 #5
mljm9
2
Thanks for the replies,
with a bit of experimenting I found this to do the job:
select * from [info] where CInt(IIF(IsNull(ValX),0,ValX)) <= 100 and CInt(IIF(IsNull(ValX),0,ValX)) > 0 and CInt(IIF(IsNull(ValY),0,ValY)) >= 100;
Feb 3 '15 #6

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

Similar topics

5
by: Mark Dicken | last post by:
Hi All, I am trying to Pass A Collection To A FUNCTION ??? (Access 2000) I have a Class called BO (for Business Objects) Within BO I have a Function called ShowCollection Public Sub...
1
by: Charles | last post by:
I have a query in access 97 that pulls names and associates those names with dates. I'd like to have a calendar function that would simply display the names under the correct date listed. ...
0
by: ss | last post by:
i read a few posts about global function access. well i am not interested in global functions. rather, i am seeking for a way to may my call in ASPX pages but not the code behind. for example:...
2
by: pkpatil | last post by:
Hi, Can a private composite object in a class access private or protected members of base class? For e.g. class composite { void memberFunction(); };
1
by: tingirl76 | last post by:
I am looking for a function that behaves like the MID function in access. I need to return all values in a column that has a specific charater in a specific location within the string. Example: I...
1
by: thavaht | last post by:
I’ve been searching on the forum and don’t seem to find a post that matches my question: I need to restrict access to some php pages on a web site to computers on the same domain or local network,...
6
by: emre esirik(hacettepe computer science and enginee | last post by:
I need to function for polynom device, there are 2 polynom and its power max=5, and polynom array is integer I need to function which device two polynom , (two polynom device with not remaining
3
by: dolphin | last post by:
Hello everyone! Can a static member function access non-static member? I think it is illegal.Is it right?
8
by: mguy27 | last post by:
We have about 2 dozen security officers who patrol about 120 buildings. They find defective or unlocked doors and write a "Trouble Report" on the door. This card gets turned in, where I have designed...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.