need a workaround for Nz function in MS Access

[mljm9]

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?

[twinnyfo]

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.

[Rabbit]

Can't you fix the data and data structure?

If not, you can try the IIF() function, COALESCE() function, or CASE control flow statement.

[Seth Schrock]

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.

[jforbes]

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.

[mljm9]

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;