Select lower case

"GARY" <grollins7@hotmail.com> wrote in message <news:3df8d4a7$1@tnews.web.dev-archive.com>...

> Hi all,
>
> I want to write a select statement that only selects records with lower case
> "s" (asc code 115).
>
> SELECT CInt(Asc([location])) AS Expr1
> FROM MIF
> WHERE (((MIF.UOI)="CT") AND ((CInt(Asc([location])))=115));
>
> This does not work giving me the message "Data type mismatch in criteria
> expression."

I usually do this sort of thing in two stages, so you can at least
get some benefit from having an index on [location]:

SELECT [location]
FROM MIF
WHERE MIF.UOI="CT"
AND [location] like 's*'
AND strcomp([location], 's', 0) > 0

If you're using ADO, you might need to use 's%' instead of 's*'.
Still, it should be able to use an index to optimize the LIKE.

--
Joe Foster <mailto:jlfoster%40znet.com> Space Cooties! <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!