ALLTRIM( ) in SQL statement

Given the following table:

CREATE TABLE mytable (subject C(10), unit C(10))

INSERT INTO mytable (subject, unit) VALUES ('algebra1',' ')
INSERT INTO mytable (subject, unit) VALUES ('algebra1','1')
INSERT INTO mytable (subject, unit) VALUES ('algebra1','10')

I tried the following SQL statement:

SELECT subject, unit, COUNT(unit) as unitcount ;
FROM mytable ;
WHERE INLIST(unit,'10') ;
GROUP BY subject, unit

The results told me correctly that there was 1 value of '10' in the
table.
===================
Question: Why does the same SQL statement with an added ALLTRIM in
the
WHERE clause result in a response consisting of 3 rows?

SELECT subject, unit, COUNT(unit) as unitcount ;
FROM mytable ;
WHERE INLIST(ALLTRIM(unit),'10') ;
GROUP BY subject, unit

Thanks,

David

.

Relevant Pages

  • Re: ALLTRIM( ) in SQL statement
    ... I tried the following SQL statement: ... SELECT subject, unit, COUNTas unitcount; ... Gene Wirchenko ... Computerese Irregular Verb Conjugation: ...
    (microsoft.public.fox.programmer.exchange)
  • RE: Queries returning with missing info (plus asks query question
    ... Let’s start with the simple query first. ... are aggregate functions, but not reserved words. ... what is at the front of the SQL statement. ... Parameter clause at the top. ...
    (microsoft.public.access.queries)
  • Re: formula help please
    ... primary and foreign keys are essentials in solving such problems. ... each of the tables are related to each other with each SQL statement. ... engine like Oracle), the program adds additional lines to the actual ... WHERE clause that describes how the tables are to be linked together. ...
    (comp.databases.oracle.misc)
  • Re: query based on form
    ... Typically it's only the WHERE clause of the query that changes so you can build that dynamically, patch it into the SQL statement, and assign it to the SQL property of the QueryDef you use for export. ... they can choose one Sales Rep, all cities, all states, all counties, just one type of business and one renewal month. ...
    (microsoft.public.access.queries)
  • Re: Using SQL in Sub to fill box on mouseclick
    ... Your SQL statement is incorrect. ... The WHERE clause comes before the GROUP BY clause. ... > filled with a query telling me which people are scheduled to work a shift. ... > Private Sub Ctl8a_4pb_Click ...
    (microsoft.public.access.formscoding)
Loading