simple query not working-urgent please

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

It gives me a "type mismatch error"

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

It gives me a "type mismatch error"

Thanks
.
Click to expand...

If you copied the SQL exactly then your problem is a
missing quote in the IN clause. If that's not the problem,
then you need to look at your Item field and make sure its
a text field.

Scott<>
 
It is text and there are quotes in the in clause!!!
-----Original Message-----
-----Original Message-----
SELECT Sum([Sales temp].Sales) AS Sales, Date()-1
FROM [Sales temp]
HAVING ([Sales temp]![item] Not in ("25773621","afy04890));

It gives me a "type mismatch error"

Thanks
.
Click to expand...

If you copied the SQL exactly then your problem is a
missing quote in the IN clause. If that's not the problem,
then you need to look at your Item field and make sure its
a text field.

Scott<>
.
Click to expand...
 
It is text and there are quotes in the in clause!!!
Click to expand...

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
 
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-----
It is text and there are quotes in the in clause!!!
Click to expand...

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
.
Click to expand...
 
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"));
Click to expand...

Two questions:

- Does it work if you change the ! to . in the WHERE clause?
- Is [item] a Lookup field? If so, it does not actually contain the
text which it appears to contain. The Lookup Wizard (annoyingly)
conceals the actual contents of the table - a numeric ID; but that's
what you need to use in the query.

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