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 <me@privacy.com> wrote in message news:<yzAgd.6142$kM.2934@newsread3.news.pas.earthl ink.net>...[color=blue]
> -----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:
>[color=green]
> > 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?[/color][/color]
