Access Comparison Operators

[Thats Me]

TMALSS: Task With maintenance of Access Database I did not develop,
don't ask about non-commented code problems or where data dictionary
is (LOL).

Have table with Inventory data for all Publications held,
[tblPubInv.InvNbr] contains binder number on shelf and is text data
in numeric/alpha form i.e.; 123, 1234, 123a, 1234c.

I want to stuff the last character of [tblPubInv.InvNbr] into a new
column [tblPubInv.BndNbrExt] if it is alpha. In addition, will later
convert InvNbr to a numeric field.
(As it is creates the last, first name in one column situation).

I thought I knew something about Comparison Operators in access but
am now am not sure (I like to test prior to committing to code and I
prefer "true" over "false" on conditions), the first query I did
returned all the records in the table

Returns all 3867 records
SELECT ALL tblPubInv.PubNbr, tblPubInv.InvNbr
FROM tblPubInv
WHERE ((((Right([tblPubInv]![InvNbr],1)>Chr(64)))=True))
ORDER BY tblPubInv.InvNbr, tblPubInv.PubNbr;

Seems to me that the above should filter out records with ASCII
values of Chr(0) thru Chr(64)

So I rephrased my query and got the 380 records with an alpha
character in last position (what I want)
SELECT ALL tblPubInv.PubNbr, tblPubInv.InvNbr
FROM tblPubInv
WHERE ((((Right([tblPubInv]![InvNbr],1)<Chr(65)))=False))
ORDER BY tblPubInv.InvNbr, tblPubInv.PubNbr;

The above works, filters out records with last position ASCII value
of Chr(0) thru Chr(64)

Returns the 380 records with an alpha character in last position
(what I want)
SELECT ALL tblPubInv.PubNbr, tblPubInv.InvNbr
FROM tblPubInv
WHERE ((((Right([tblPubInv]![InvNbr],1)>=Chr(65)))=True))
ORDER BY tblPubInv.PubNbr, tblPubInv.InvNbr;

The above also works, filters out records with last position ASCII
value of Chr(0) thru Chr(64)

What the heck am I missing on how Access Comparison operators work?
Have a nice day. ld****@NOPANTS.juno.com

Remove NOPANTS. To reply by direct E-Mail;
Support: The Right to Privacy and Anti-SPAM projects

[MGFoster]

Thats Me wrote:

TMALSS: Task With maintenance of Access Database I did not develop,
don't ask about non-commented code problems or where data dictionary
is (LOL).

Have table with Inventory data for all Publications held,
[tblPubInv.InvNbr] contains binder number on shelf and is text data
in numeric/alpha form i.e.; 123, 1234, 123a, 1234c.

I want to stuff the last character of [tblPubInv.InvNbr] into a new
column [tblPubInv.BndNbrExt] if it is alpha. In addition, will later
convert InvNbr to a numeric field.
(As it is creates the last, first name in one column situation).

I thought I knew something about Comparison Operators in access but
am now am not sure (I like to test prior to committing to code and I
prefer "true" over "false" on conditions), the first query I did
returned all the records in the table

Returns all 3867 records
SELECT ALL tblPubInv.PubNbr, tblPubInv.InvNbr
FROM tblPubInv
WHERE ((((Right([tblPubInv]![InvNbr],1)>Chr(64)))=True))
ORDER BY tblPubInv.InvNbr, tblPubInv.PubNbr;

Seems to me that the above should filter out records with ASCII
values of Chr(0) thru Chr(64)

So I rephrased my query and got the 380 records with an alpha
character in last position (what I want)
SELECT ALL tblPubInv.PubNbr, tblPubInv.InvNbr
FROM tblPubInv
WHERE ((((Right([tblPubInv]![InvNbr],1)<Chr(65)))=False))
ORDER BY tblPubInv.InvNbr, tblPubInv.PubNbr;

The above works, filters out records with last position ASCII value
of Chr(0) thru Chr(64)

Returns the 380 records with an alpha character in last position
(what I want)
SELECT ALL tblPubInv.PubNbr, tblPubInv.InvNbr
FROM tblPubInv
WHERE ((((Right([tblPubInv]![InvNbr],1)>=Chr(65)))=True))
ORDER BY tblPubInv.PubNbr, tblPubInv.InvNbr;

The above also works, filters out records with last position ASCII
value of Chr(0) thru Chr(64)

What the heck am I missing on how Access Comparison operators work?

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

You may wish to use a more SQL compliant criteria:

SELECT PubNbr, InvNbr
FROM tblPubInv
WHERE InvNbr LIKE "*[a-z]"
ORDER BY PubNbr, InvNbr

Using Like "*[a-z]" means select all rows where the column InvNbr value
ends with the any letters between a and z. Since Access is not case
sensitive you can use either [a-z] or [A-Z] and it will recognize any
case letters.

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

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

iQA/AwUBQbOeVYechKqOuFEgEQJNHACZARYBG38JA/2zftSgdPJAyNqJh0cAnjR1
qPOqscni0+174gmLnHB3toKQ
=Sh8o
-----END PGP SIGNATURE-----