query using a +/- value? 1

[jdonaway]

I'm pretty inexperienced so I hope I'm phrasing my questions correctly. But I'm creating a database at work and ultimately, the enduser will be entering data in a form, and then viewing a print preview report listing the results of the query, which is run based on what they entered in the form. I have seen information on how to do sum, totals etc. But for example, one of the values entered in the form, I need the query to search for that value, plus or minus 10%. What is the easiest way to code this? I appreciate any help offered.

 

[JerryDennison]

You can use parameters within queries. For numeric or date values(which are themselves basically numeric) you can use the 'Between' keyword. So, for example, if you have a control on your form and you want to find a value in your query that is within +/- 10% of this value your criteria would look something like the following:

Between (Forms!FormName!ControlName * 1.1) And (Forms!FormName!ControlName * .9)

 

[jdonaway]

Thank you! That seems to have worked. Now the remainder of the fields, they want the results to be like a -0/+5%, another is +5,-0, so for one like that, would it be better to do a greater than?

 

[JerryDennison]

Try for -0/+5%:

=< [CriteriaValue] * 1.05

and for +5, -0:

=< [CriteriaValue] + 5

 

[jdonaway]

ok, I tried that, and I get an error message that says &quot;the expression you entered contains invalid syntax. You may have entered a comma w/o a preceding value or indentifier. The expression I entered under criteria was: =< [BW] * 1.05, with BW being the control name of the box on my form, where this data is being entered. Did I do something incorrectly? Again, I thank you so much for your help!

jdonaway@klmicrowave.com

 

[pjm]

Sorry to butt in on your thread! I think the operator should be &quot;<=&quot; not &quot;=<&quot;

pjm

 

[jdonaway]

that's seems to be working! Thanks so much! I have another question about my form and checkboxes but I'll wait til I get the rest of my query working.

 

[jdonaway]

ok, now this is where it gets tricky. I have a drop down list, where the user will select a value from 20-60. (increments of 20; 20, 30, up to 60), then in another box on the form, they will enter a low number and a high number. In my table I have a 20 low field, a 20 high field, a 30 low field, a 30 high field, etc. So ultimately, the user wants to select either 20, 30 or whatever, enter in the low and high number and retrieve the results. Is this something I can achieve with criteria in my query or does the coding get more difficult? If I'm not making any sense, please email me. Thanks in advance!!