Calculating Values in a Query

  • Thread starter Thread starter tsquared1518
  • Start date Start date
T

tsquared1518

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!!
 
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.
 
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.
 
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
 
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.
 
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]"));
Click to expand...

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?
 
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.
Click to expand...

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.
 
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?
 
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?
Click to expand...

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.
 
Back
Top