If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
simple query not working-urgent please
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 |
#2
|
|||
|
|||
-----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 . 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 |
#3
|
|||
|
|||
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 . 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 . |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 . |
#6
|
|||
|
|||
On Mon, 27 Sep 2004 11:24:13 -0700,
wrote: 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")); 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Display Parameter from Form on Report | sara | Setting Up & Running Reports | 10 | July 19th, 2004 04:54 PM |
Crosstab Query -- Urgent!! Help needed....... | bpan007 | Setting Up & Running Reports | 1 | July 13th, 2004 04:50 PM |
Hidden files in Ms-Query cause ODBC connect errors or Query is wac | needyourhelp | General Discussion | 4 | July 12th, 2004 09:38 PM |
query field reference help | -dch | Running & Setting Up Queries | 4 | June 2nd, 2004 07:30 PM |