All I want to do is pick up Sales of items that are not in
the list. The same query works with "in" but gives
me "data type mismatch in criteria expression". What do I
do?

SELECT Sum([Sales temp].Sales) AS Sales
FROM [Sales temp]
WHERE ([Sales temp]![item] Not in ("25773621","afy04890"));
-----Original Message-----
On Mon, 27 Sep 2004 10:21:17 -0700,
wrote:

It is text and there are quotes in the in clause!!!

The SQL you posted has one too few quotes:

SELECT Sum([Sales temp].Sales) AS Sales, Date()-1
FROM [Sales temp]
HAVING ([Sales temp]![item] Not in
("25773621","afy04890));

There is a quote before afy04890 but there is no quote
after it.

You may also want to change the word HAVING to WHERE. The
WHERE clause
is applied before the summing operations; the HAVING
clause does all
the sums, and only then applies the criterion. To do this
in the grid
select the [Item] field and set its Total operation to
WHERE.

I'm not sure what you expect here. You're summing the
value of SALES
but you're not grouping by anything - and you're
including yesterday's
date in the query but not referencing any table date/time
fields!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
.