Urgent! Query: "Invalid Use of Null" Access 2002 cDBL

[Dave Harkness]

In Access 2002, I have a query that compares a string value (zip) to a
numeric value if the zip field is not null and it is numeric:

SELECT * FROM contacts
WHERE zip IS NOT NULL
AND IsNumeric(Left([ZIP],5))
AND clng(LEFT(zip,5)) < 50000

When executed I get an
"Invalid use of Null"
AND a
"Data type mismatch in expression"
error.

Can anyone tell me what the heck is going on?
I would be eternally grateful :-)

I would appreciate a response directly to:
Dave_REM...@garciaoliveoils.com
(Dave@)

[Nath Wilson]

>SELECT * FROM contacts
>WHERE zip IS NOT NULL
>AND IsNumeric(Left([ZIP],5))
>AND clng(LEFT(zip,5)) < 50000

I could be wrong, but it sounds like access is using your criteria on
all values of zip, not just the non-null ones.

Try putting the other two zip instances in a Nz something like,

IsNumeric(Left(Nz(Zip,""),5)) AND clng(left(zip,5)) <50000

Tho it may still through an error there, i have no data to test it.

Then it may be best if you were to nest the queries, the first one being

SELECT * FROM contacts
WHERE zip is Not Null

and the second one performing your other two criteria.

>I would appreciate a response directly to:
>Dave_REM...@garciaoliveoils.com
>(Dave@)

The question was here, so is an answer :)

--
HTH

Nath

(Email mangling obvious)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Peter Russell]

In article <11c11998.03050...@posting.google.com>,
Da...@garciaoliveoils.com (Dave Harkness) wrote:

> SELECT * FROM contacts
> WHERE zip IS NOT NULL
> AND IsNumeric(Left([ZIP],5))
> AND clng(LEFT(zip,5)) < 50000

The problem is with the last condition, although I can't explain it
fully.
First, be sure there ARE 5 chars to select, by appending spaces to the
value.
To convert to a long the leftmost 5 MUST be convertible.
Val will give you the number where it can get it, and combined with your
other criteria should be OK.
But test it for yourself, to be sure.

AND Val(Left([zip] & " ",5))<50000

Regards

Peter Russell

[Serge Gavrilov]

SELECT *
FROM contacts
WHERE zip Is Not Null AND IsNumeric(Left(nz([ZIP],0),5))<>False AND
CLng(Left(nz([zip],0),5))<50000;