Invalid use of Null

[headware]

I'm getting a weird problem in an Access query. I have a table that
contains a field calle F1 that's a 2 character text field. The first
character is always a number. What I'd like to do is find all the
records in which F1 has an even number in the first position. I first
tried this:

SELECT F1
FROM Table1
WHERE Nz(F1, '') <> '' AND (CInt(Left(F1, 1)) Mod 2 = 0)

This gave me the error "Invalid use of null". I figured this didn't
work because some of the F1 entries are blank and SQL doesn't short
circuit AND statements, so it's trying to take the Left of a blank
field. So I tried breaking it up into more than one query. I created a
query called qryA that went like this:

SELECT Left(F1, 1) AS F1Char1
FROM Table1
WHERE Nz(F1, '') <> '';

Then I created another query like this:

SELECT *
FROM qryA
WHERE CInt(F1Char1) Mod 2 = 0

Still get "Invalid use of null".

The funny part is that if I do this:

SELECT CInt(F1Char1) Mod 2 = 0
FROM qryA

It works. No invalid null. I guess that's not really that funny. But
here's something that *is* funny: if I run the following query it
works!

SELECT *
FROM qryA
WHERE (CInt(F1Char1) Mod 2 = 0) LIKE '-1'

Why would it work using LIKE? What's going on here?

Thanks,
Dave

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

How's about:

SELECT Left(F1,1) AS F1Char1
FROM Table1
WHERE F1 IS NOT NULL
AND Val(Nz(F1,0)) MOD 2 = 0

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQYLVb4echKqOuFEgEQIByACffhHJ4GL6qH5JPidzJ+C16A pUFikAn2JT
lbm9JtWq77n6FFnQVB7nw2XV
=qNxx
-----END PGP SIGNATURE-----
headware wrote:

I'm getting a weird problem in an Access query. I have a table that
contains a field calle F1 that's a 2 character text field. The first
character is always a number. What I'd like to do is find all the
records in which F1 has an even number in the first position. I first
tried this:

SELECT F1
FROM Table1
WHERE Nz(F1, '') <> '' AND (CInt(Left(F1, 1)) Mod 2 = 0)

This gave me the error "Invalid use of null". I figured this didn't
work because some of the F1 entries are blank and SQL doesn't short
circuit AND statements, so it's trying to take the Left of a blank
field. So I tried breaking it up into more than one query. I created a
query called qryA that went like this:

SELECT Left(F1, 1) AS F1Char1
FROM Table1
WHERE Nz(F1, '') <> '';

Then I created another query like this:

SELECT *
FROM qryA
WHERE CInt(F1Char1) Mod 2 = 0

Still get "Invalid use of null".

The funny part is that if I do this:

SELECT CInt(F1Char1) Mod 2 = 0
FROM qryA

It works. No invalid null. I guess that's not really that funny. But
here's something that *is* funny: if I run the following query it
works!

SELECT *
FROM qryA
WHERE (CInt(F1Char1) Mod 2 = 0) LIKE '-1'

Why would it work using LIKE? What's going on here?

[headware]

Thanks, that worked perfectly. I guess this must come down to how the
Val function handles null or blank data. It looks as if it returns 0
in that case.

Dave

MGFoster <[e-mail]> wrote in message news:<yz****************@newsread3.news.pas.earthl ink.net>...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

How's about:

SELECT Left(F1,1) AS F1Char1
FROM Table1
WHERE F1 IS NOT NULL
AND Val(Nz(F1,0)) MOD 2 = 0

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQYLVb4echKqOuFEgEQIByACffhHJ4GL6qH5JPidzJ+C16A pUFikAn2JT
lbm9JtWq77n6FFnQVB7nw2XV
=qNxx
-----END PGP SIGNATURE-----
headware wrote:

I'm getting a weird problem in an Access query. I have a table that
contains a field calle F1 that's a 2 character text field. The first
character is always a number. What I'd like to do is find all the
records in which F1 has an even number in the first position. I first
tried this:

SELECT F1
FROM Table1
WHERE Nz(F1, '') <> '' AND (CInt(Left(F1, 1)) Mod 2 = 0)

This gave me the error "Invalid use of null". I figured this didn't
work because some of the F1 entries are blank and SQL doesn't short
circuit AND statements, so it's trying to take the Left of a blank
field. So I tried breaking it up into more than one query. I created a
query called qryA that went like this:

SELECT Left(F1, 1) AS F1Char1
FROM Table1
WHERE Nz(F1, '') <> '';

Then I created another query like this:

SELECT *
FROM qryA
WHERE CInt(F1Char1) Mod 2 = 0

Still get "Invalid use of null".

The funny part is that if I do this:

SELECT CInt(F1Char1) Mod 2 = 0
FROM qryA

It works. No invalid null. I guess that's not really that funny. But
here's something that *is* funny: if I run the following query it
works!

SELECT *
FROM qryA
WHERE (CInt(F1Char1) Mod 2 = 0) LIKE '-1'

Why would it work using LIKE? What's going on here?