|
| 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
September 13th, 2004, 10:32 PM
|
|||
|
|||
DATA TYPE MISMATCH
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. 
|
|
#2
September 13th, 2004, 10:36 PM
|
|||
|
|||
|
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? -- Duane Hookom MS Access MVP -- "Sandra" wrote in message ... 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.
|
|
#3
September 13th, 2004, 11:11 PM
|
|||
|
|||
|
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? -----Original Message----- 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? -- Duane Hookom MS Access MVP -- "Sandra" wrote in message ... 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. .
|
|
#4
September 14th, 2004, 12:50 AM
|
|||
|
|||
|
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. -- Duane Hookom MS Access MVP "Sandra" wrote in message ... 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? -----Original Message----- 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? -- Duane Hookom MS Access MVP -- "Sandra" wrote in message ... 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. .
|
|
#5
September 14th, 2004, 03:53 AM
|
|||
|
|||
|
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! -----Original Message----- 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. -- Duane Hookom MS Access MVP "Sandra" wrote in message ... 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? -----Original Message----- 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? -- Duane Hookom MS Access MVP -- "Sandra" wrote in message ... 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. . .
|
|
#6
September 14th, 2004, 03:26 PM
|
|||
|
|||
|
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); -- Duane Hookom MS Access MVP -- "Sandra" wrote in message ... 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! -----Original Message----- 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. -- Duane Hookom MS Access MVP "Sandra" wrote in message ... 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? -----Original Message----- 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? -- Duane Hookom MS Access MVP -- "Sandra" wrote in message ... 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. . .
|
|
#7
September 14th, 2004, 06:50 PM
|
|||
|
|||
|
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! -----Original Message----- 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); -- Duane Hookom MS Access MVP -- "Sandra" wrote in message ... 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! -----Original Message----- 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. -- Duane Hookom MS Access MVP "Sandra" wrote in message ... 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? -----Original Message----- 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? -- Duane Hookom MS Access MVP -- "Sandra" wrote in message ... 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. . . .
|
|
#8
September 16th, 2004, 05:46 PM
|
|||
|
|||
|
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. -- Duane Hookom MS Access MVP -- "Sandra" wrote in message ... 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! -----Original Message----- 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); -- Duane Hookom MS Access MVP -- "Sandra" wrote in message ... 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! -----Original Message----- 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. -- Duane Hookom MS Access MVP "Sandra" wrote in message ... 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? -----Original Message----- 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? -- Duane Hookom MS Access MVP -- "Sandra" wrote in message ... 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. . . .
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Images in a database | Franz | General Discussion | 10 | October 7th, 2004 09:35 AM |
| data type mismatch error | Praveen Manne | Using Forms | 3 | June 30th, 2004 11:02 PM |
| charting date and time data (every minute type) | econair | Charts and Charting | 1 | February 6th, 2004 02:30 PM |
Linear Mode
