DATA TYPE MISMATCH

  • Thread starter Thread starter Sandra
  • Start date Start date
S

Sandra

When I enter "Max" in the criteria line of a query, I get
the pop-up message, "Data type mismatch".

Why, when the field is numeric? All the fields are
numeric except for month which is text.
 
A numeric field will never have the value "Max". You are attempting to
compare numeric values to a text value so you get the error.

What are you attempting to do?
 
Thank you for responding, Duane.

I want the maximum value in a field called "sales_R" or a
field called "sales_C", which means the sales of a
particular location. I thought "Max" would refer to
numeric items. Is there another that does?
 
Why don't you describe your data and fields and give us some sample records?
Then type in the expected results based on your sample records.
 
Here are fields followed by sample data:

Year Month Chicago Charlotte New York Nashville
1992 Jan 200,000 15,000 25,000 45,000
1992 Feb 120,000 17,000 35,000 15,000
1992 Mar 100,000 12,000 18,000 20,000
1992 Apr 250,000 15,200 16,000 18,000
1992 May , etc
1993 Jan 150,000 12,000 19,000 17,000

Similar for years 1994,1995,1996 on to 2004
There would be a total of 15 cities.

The output could be

Highest Sales for Chicago for a particular month
Year Month Sales
1999 Jan 300,000
2000 Feb 100,000
1992 Mar 100,000
2004 Apr 600,000
on to Dec

Highest Sales for Nashville for a particular month
1992 Jan 45,000
2001 Feb 20,000
1993 Mar 17,000
etc. on to Dec

Thank you!
 
I don't care much for your "spreadsheet" table structure. I would create a
table with fields: Year, Month, City, SalesAmt. I would also store month as
the month number so Jan = 1, Feb=2,...

However, the following will get you the year, month, and sales of the
highest sales for a given month for Chicago:

SELECT Year, Month, Chicago AS Sales
FROM tblSampleData
WHERE Chicago=
(SELECT Max(Chicago)
FROM tblSampleData sd
WHERE sd.Month = tblSampleData.Month);
 
Where would I put that code? I'm used to working in the
form that comes up when you choose query, and I know a
LITTLE about using the expression builder.
Thanks again!
 
You could do this in the query design view or SQL view. The second select
statement "(SELECT Max(....)...)" would be typed into the criteria under the
Chicago column/field.
 
Back
Top