Query Criteria in MS Access

I have a text box that I am entering an end value into, say 15.

I am using this text box in a query as criteria - = Forms![frmReports]![txtEndCriteria].

I would like to return all values that are less than 15 - the value I enter into the text box.

One problem.  The field in the query that I am using for criteria is a text field (which I can't change).  This not allowing me to return the proper values.  It seems to view 999 as 9 and 199 as 1 - therefore my query is returning the wrong values.

Any suggestions?
jamesdean666Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TheSloathCommented:
In the Query Rather than having the WHERE clause directly on the text field, e.g.:

[txtNumber] < Forms![frmReports]![txtEndCriteria]

...change it into a number first, e.g.:

CDbl([txtNumber]) < Forms![frmReports]![txtEndCriteria]
0
TheSloathCommented:
The reason this is happening is because computers compare numbers and text differently.

The computer doesn't care how many letters are in the text, it literally compares 2 words letter by letter starting at the beginning, until one, e.g. to a computer:

"d" > "aaaaaa"    Even though "aaaaaa" has more letters, "d" is higher than "a" and 'wins' immediately.

...so in your example:

"2","3","4","5","6","7","8","9" are all larger than "15" because "15" begins with a 1...
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:

Change your query field (or add this expression) to:
CInt([YourQueryFieldName])

And your criteria expression for the above to:

<=CInt(Forms![frmReports]![txtEndCriteria])

If you are entering non-integers in the text box, then use CSng instead of CInt

mx
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

jamesdean666Author Commented:
None working....
0
TheSloathCommented:
Can you post the SQL you are running?
0
jamesdean666Author Commented:
SELECT tlkpClinic.Clinic, QRY_DOH_SummaryDiabetes.pcp, QRY_DOH_SummaryDiabetes.chart_number, tblDemo.[last_name] & ", " & [first_name] AS FullName, tblDemo.phone1, tblDemo.address1, tblDemo.address2, tblDemo.city, tblDemo.state, tblDemo.zip, QRY_DOH_AllDiabetesInclA1CTests.last_result, QRY_DOH_SummaryDiabetes.clinic_code
FROM ((QRY_DOH_SummaryDiabetes INNER JOIN tblDemo ON (QRY_DOH_SummaryDiabetes.chart_number = tblDemo.chart_number) AND (QRY_DOH_SummaryDiabetes.clinic_code = tblDemo.clinic_code)) INNER JOIN tlkpClinic ON QRY_DOH_SummaryDiabetes.clinic_code = tlkpClinic.Clinic_code) LEFT JOIN QRY_DOH_AllDiabetesInclA1CTests ON (QRY_DOH_SummaryDiabetes.clinic_code = QRY_DOH_AllDiabetesInclA1CTests.clinic_code) AND (QRY_DOH_SummaryDiabetes.chart_number = QRY_DOH_AllDiabetesInclA1CTests.chart_number)
WHERE (((QRY_DOH_SummaryDiabetes.clinic_code)<=CDbl([Forms]![frmReports]![txtEndCriteria])))
ORDER BY CSng(nz([last_result],999));
0
TheSloathCommented:
You need a CDblon the QRY_DOH_SummaryDiabetes.clinic_code, as it is a text field:


WHERE ((CDbl(QRY_DOH_SummaryDiabetes.clinic_code)<=CDbl([Forms]![frmReports]![txtEndCriteria])))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TheSloathCommented:
Just a side note: if there was an index on QRY_DOH_SummaryDiabetes.clinic_code, by performing a conversion on it in the WHERE clause you would loose the benifit of the index. If there was a large amount of data and this caused a performance hit, it would better to store the clinic_code with leading zeros, so 9 could be 00009 and 234 could be 00234 for example, then you could have the following WHERE clause, keeping the benifit of the index:


WHERE (((QRY_DOH_SummaryDiabetes.clinic_code)<=Format([Forms]![frmReports]![txtEndCriteria], "00000")))
0
jamesdean666Author Commented:
Rock and Roll oh mighty Sloath...

and many thanks!

JD
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Basically what I said, lol ...
"Change your query field (or add this expression) to:
CInt([YourQueryFieldName])"

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.