A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

simple query not working-urgent please



 
 
Thread Tools Display Modes
  #1  
Old September 27th, 2004, 02:10 PM
external usenet poster
 
Posts: n/a
Default 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  
Old September 27th, 2004, 04:26 PM
external usenet poster
 
Posts: n/a
Default


-----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  
Old September 27th, 2004, 06:21 PM
external usenet poster
 
Posts: n/a
Default

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  
Old September 27th, 2004, 06:46 PM
John Vinson
external usenet poster
 
Posts: n/a
Default

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  
Old September 27th, 2004, 07:24 PM
external usenet poster
 
Posts: n/a
Default

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  
Old September 27th, 2004, 09:10 PM
John Vinson
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 03:06 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.