Query Range

  • Thread starter Thread starter robbywvut
  • Start date Start date
R

robbywvut

Using Access 2003.
Is there any way to create a query that looks at the value of a text
box and queries a table for values within 20%?
I have a form with a number of combo boxes and text boxes to feed and
run a query. I can't seem to find any information on querying for a
range of values however.
Any help is much appreciated. Thanks in advance.
 
Presumably you have a field named Amount in your query (a Number type
field), and you want all values within 20% lower and higher than the value
in the text box named Text1 on Form1.

20% lower would be 4/5ths. 20% higher would be 6/5ths.

In the Criteria row of your query under this number field, enter:
Between (4 * [Forms].[Form1].[Text1] / 5) And (6 * [Forms].[Form1].[Text1] /
5)

Notes:
====
1. You can run into problems with Access misunderstanding the data types
here. Declare your parameter, by choosing Parameters on the Query menu, and
entering a line into the dialog like this:
[Forms].[Form1].[Text1] Double
Also, if Text1 is unbound, set its Format property to General Number so it
accepts only numbers.

2. The above won't work if you leave the text box blank. If you have to cope
with that, switch the query to SQL View (View menu), and change the WHERE
clause so it looks like this:
WHERE (([Forms].[Form1].[Text1] Is Null) OR ([Amount] Between
(4 * [Forms].[Form1].[Text1] / 5) And (6 * [Forms].[Form1].[Text1] / 5)))
 
Allen, That worked like a charm. However, I'm now running into more
problems. The query field we discussed earlier was one of 9 run from
the same form. Seven of these fields are combo boxes and the final two
are text boxes. I need to do the same type of "range" search on the
final two fields. When I create the query, however, I get a message
telling me the "Query is too complex". Looking at the SQL view, I can
see why.
Is there any way I can simplify the query while running these fields
from the same form?
Thanks again for your help!
 
If all 18 controls on the form had the correct type of value present, there
would be only 9 ANDs on the SQL statement--well short of the 100
permitted--so it is theoretically possible to do what you asked.

In practice, though these controls could contain any kind of data if they
are unbound, or JET could misintrepret the values, especially if as the
parameters are undeclared. You can improve this situation by setting the
Format property of the unbound controls, and declaring the parameters so
they are typed (Parameters on the Query menu, in query design view.)

That still doesn't deal with what happens if any one of these controls does
not contain a value. At that point, the SQL statement will fall apart,
unless you add Nz() functions around each of the values to cope with Null,
but that introduces another range of problems.

The best solution is going to be to redesign this interface so that the
WHERE clause is built up dynamically from only those controls that actually
have a value. How you do that will depend on where this query is headed. If
it is for a report, you could leave all the criteria out of the query, and
use the WhereCondition for the OpenReport action. For an example, see the
2nd method in this article:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

If the form is to filter records for a form, you could build up a string in
exactly the same way, and apply it to the Filter property of your form
(rembering to set FilterOn as well.)

If the query really is needed to base another query on, you could even write
the SQL property of the QueryDef.

Sorry: that's not a simple answer. It wasn't really a simple question.
 
Allen,

I understand it's not a simple question, but I appreciate the answers.
I think I will try declaring my parameters before doing anything else.
My combo boxes and text boxes are all unbound. The combo boxes are all
based on another query so that the user's choices are limited based on
prior selections.
Also, the results of this query will be headed to another query. The
query I'm working on now looks at construction project information
(location, cost, traffic volume, etc.). The query looks through my
Project Info table and returns the project numbers with the parameters
specified. Once I get this one working correctly, another form will be
used, depending on the "type" of project, to narrow this list of
projects further.
That said, I will also be looking into writing the SQL property as you
suggested. Thanks again!
 
Allen,

Is it possible to dynamically build a query through the query design
view? Up to this point I've been building my query in design view,
staying away from VBA as much as possible for simplicity sake (both on
my end and the eventual db administrator's). Is there a way I can
dynamically build the query this way?

Thanks again for your help!
 
You can mock up a query in the design window, plugging in any literal values
as criteria. Then switch to SQL View (View menu). You can copy most of what
you see there and paste into your VBA code.

It still involves some cleaning up: add the surrounding quotes (doubling up
any embedded quotes). Usually the WHERE clause is the bit that needs
modifying most, because you are patching in conditions to match whatever
criteria you need at the time, and getting the delimiters right (quotes for
text, # for dates, none for numbers.)

So, yes, the query design window is still a useful way to get these
statements generated.
 
Allen,

One more interesting question and I promise that's it...for today. I
should have trusted your original response (to use fractions to
manipulate the "Between" statement). However, I've been making some
revisions to what I want returned and so I decided that +- 15% might be
better. Instead of making a fraction, I used decimals (0.85*Text Box
Value etc). WHen I did this, I recieved the huge SQL statement as well
as the "Too Complex" error.
As a last ditch effort, I tried inputing a fraction instead of a
decimal and everything works just fine. My SQL is reduced tremendously
and everything with the query seems to be working just fine.
If you have the time, could you explain this "phenomenon" to me?
Could the reason be that the txtBox value is formatted as an Integer
with Zero decimal places? Just curious, I'm trying to learn all I can
about the inner workings of Access.

Again, thanks for all your help!!! Have a good day!
 
Percentages are fractions of one hundred. You therefore need to use
fractional numbers, not integer values which can be whole numbers only.

As you found, it is important to help JET to understand the intended data
types of your fields. The "too complex" error often means that it is not
able to make sense out of the data types. I'm not sure how or why the SQL
statement became longer when you had not helped it to understand the types.
 
Back
Top