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
|
|||
|
|||
Calculating Values in a Query
I have 4 fields in a query, Drawing Dim., Tolerance, Upper Limit, and Lower
Limit. I am trying to calculate the Upper and Lower Limit fields by taking the Drawing Dim. +/- the Tolerance (this works fine on my form for entering the data). The problem is that the Tolerance is a combo box (dropdown), and the user chooses from values in the table. I am pretty sure this is why I am getting a Data Type Mismatch Error in my query, but I cant figure out how to get it to work. Can someone please help me? Thanks!! |
#2
|
|||
|
|||
Calculating Values in a Query
The problem is that the Tolerance is a combo box (dropdown), and the user
chooses from values in the table. Only if values in the table are in a text field including character that are not numerials like 5% or .0025". Post the SQL of your query and sample data. -- Build a little, test a little. "tsquared1518" wrote: I have 4 fields in a query, Drawing Dim., Tolerance, Upper Limit, and Lower Limit. I am trying to calculate the Upper and Lower Limit fields by taking the Drawing Dim. +/- the Tolerance (this works fine on my form for entering the data). The problem is that the Tolerance is a combo box (dropdown), and the user chooses from values in the table. I am pretty sure this is why I am getting a Data Type Mismatch Error in my query, but I cant figure out how to get it to work. Can someone please help me? Thanks!! |
#3
|
|||
|
|||
Calculating Values in a Query
Is there a chance that you have a table with a field defined as a "lookup"
data type? That's where Access stores one value (the primary key), but displays a different value (the "looked-up value"). This leads to confusion and could be the cause of your data type mismatch error message. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "tsquared1518" wrote in message news I have 4 fields in a query, Drawing Dim., Tolerance, Upper Limit, and Lower Limit. I am trying to calculate the Upper and Lower Limit fields by taking the Drawing Dim. +/- the Tolerance (this works fine on my form for entering the data). The problem is that the Tolerance is a combo box (dropdown), and the user chooses from values in the table. I am pretty sure this is why I am getting a Data Type Mismatch Error in my query, but I cant figure out how to get it to work. Can someone please help me? Thanks!! |
#4
|
|||
|
|||
Calculating Values in a Query
SELECT [tblInspection Results].[Drawing Dimension], [tblInspection
Results].[Tolerance (+/-)], [tblInspection Results].[Upper Limit], [tblInspection Results].[Lower Limit] FROM [tblInspection Results] WHERE ((([tblInspection Results].[Upper Limit])="tblInspection Results[Drawing Dimension]"+"tblInspection Results [Tolerance]") AND (([tblInspection Results].[Lower Limit])="tblInspection Results[Drawing Dimension] - tblInspection Results[Tolerance]")); For sample data, would you like me to copy it from the table or do you just need numbers...for example, it should look like below; Drawing Dim Tolerance Upper Limit Lower Limit 1.5 .063 1.563 1.437 2.0 .098 2.098 1.902 "KARL DEWEY" wrote: The problem is that the Tolerance is a combo box (dropdown), and the user chooses from values in the table. Only if values in the table are in a text field including character that are not numerials like 5% or .0025". Post the SQL of your query and sample data. -- Build a little, test a little. "tsquared1518" wrote: I have 4 fields in a query, Drawing Dim., Tolerance, Upper Limit, and Lower Limit. I am trying to calculate the Upper and Lower Limit fields by taking the Drawing Dim. +/- the Tolerance (this works fine on my form for entering the data). The problem is that the Tolerance is a combo box (dropdown), and the user chooses from values in the table. I am pretty sure this is why I am getting a Data Type Mismatch Error in my query, but I cant figure out how to get it to work. Can someone please help me? Thanks!! |
#5
|
|||
|
|||
Calculating Values in a Query
Yep...in the table i have the tolerance field as a lookup, to pull the values
into the dropdown box so you can choose which value to use....I figured this was causing the problem...do you have any recommendations. I would like to keep the lookup or something like it in the table, because it makes it really easy to enter data in the form. Thanks for your suggestions in advance. "Jeff Boyce" wrote: Is there a chance that you have a table with a field defined as a "lookup" data type? That's where Access stores one value (the primary key), but displays a different value (the "looked-up value"). This leads to confusion and could be the cause of your data type mismatch error message. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "tsquared1518" wrote in message news I have 4 fields in a query, Drawing Dim., Tolerance, Upper Limit, and Lower Limit. I am trying to calculate the Upper and Lower Limit fields by taking the Drawing Dim. +/- the Tolerance (this works fine on my form for entering the data). The problem is that the Tolerance is a combo box (dropdown), and the user chooses from values in the table. I am pretty sure this is why I am getting a Data Type Mismatch Error in my query, but I cant figure out how to get it to work. Can someone please help me? Thanks!! . |
#6
|
|||
|
|||
Calculating Values in a Query
On Thu, 4 Mar 2010 11:16:01 -0800, tsquared1518
wrote: SELECT [tblInspection Results].[Drawing Dimension], [tblInspection Results].[Tolerance (+/-)], [tblInspection Results].[Upper Limit], [tblInspection Results].[Lower Limit] FROM [tblInspection Results] WHERE ((([tblInspection Results].[Upper Limit])="tblInspection Results[Drawing Dimension]"+"tblInspection Results [Tolerance]") AND (([tblInspection Results].[Lower Limit])="tblInspection Results[Drawing Dimension] - tblInspection Results[Tolerance]")); The quotemarks are your problem. As written the query will find those records where the field [Upper Limit] contains the literal text string "tblInspection Results[Drawing Dimension]tblInspection Results [Tolerance]" which will probably be... none at all. It appears that you're working in the wrong place. The Criteria line on the form - which it seems you're using - is for putting criteria on an existing field to restrict which records are returned. I'm guessing that the table fields Upper Limit and Lower Limit should simply not EXIST in your table; instead they should be calculated on the fly as calculated fields in the query: SELECT [tblInspection Results].[Drawing Dimension], [tblInspection Results].[Tolerance (+/-)],[Drawing Dimension] + [Tolerance] AS [Upper Limit], [Drawing Dimension] - [Tolerance] AS [Lower Limit]; Your query as written is not soliciting any input from the user - how will the user specify which records should be returned? With a parameter query, a form, or what? -- John W. Vinson [MVP] |
#7
|
|||
|
|||
Calculating Values in a Query
On Thu, 4 Mar 2010 11:53:02 -0800, tsquared1518
wrote: Yep...in the table i have the tolerance field as a lookup, to pull the values into the dropdown box so you can choose which value to use....I figured this was causing the problem...do you have any recommendations. I would like to keep the lookup or something like it in the table, because it makes it really easy to enter data in the form. Thanks for your suggestions in advance. You can have a lookup *on a form* without having a Lookup Field *in the table*. Yes, having a lookup field in the table makes it a couple of mouseclicks quicker to put a combo box on a form. As best as I can tell, that is its ONLY benefit. There are a host of detriments to weigh against it: see http://www.mvps.org/access/lookupfields.htm for a critique. -- John W. Vinson [MVP] |
#8
|
|||
|
|||
Calculating Values in a Query
I clicked the link and read why I shouldnt have it set up the way I do, but
it didnt mention any alternative ways to set it up...can someone help me out? "John W. Vinson" wrote: On Thu, 4 Mar 2010 11:53:02 -0800, tsquared1518 wrote: Yep...in the table i have the tolerance field as a lookup, to pull the values into the dropdown box so you can choose which value to use....I figured this was causing the problem...do you have any recommendations. I would like to keep the lookup or something like it in the table, because it makes it really easy to enter data in the form. Thanks for your suggestions in advance. You can have a lookup *on a form* without having a Lookup Field *in the table*. Yes, having a lookup field in the table makes it a couple of mouseclicks quicker to put a combo box on a form. As best as I can tell, that is its ONLY benefit. There are a host of detriments to weigh against it: see http://www.mvps.org/access/lookupfields.htm for a critique. -- John W. Vinson [MVP] . |
#9
|
|||
|
|||
Calculating Values in a Query
On Fri, 5 Mar 2010 05:35:01 -0800, tsquared1518
wrote: I clicked the link and read why I shouldnt have it set up the way I do, but it didnt mention any alternative ways to set it up...can someone help me out? You should not use a Lookup Field ( a combo box ) *in a table*. You certainly SHOULD use Lookups (combo boxes) *on your form*. Just use the lookup table, as a table in your database. Select the field you're now using as a lookup field in table design view, and on the Lookup tab change it from "combo box" to "textbox" to display what's actually in the table. On any Form referencing the field, use the Combo Box tool on the toolbar to insert a combo box, so you can store the ID while displaying the text value. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|