Solved

Query Criteria in MS Access

Posted on 2007-03-26
10
197 Views
Last Modified: 2013-11-25
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?
0
Comment
Question by:jamesdean666
  • 5
  • 3
  • 2
10 Comments
 
LVL 9

Expert Comment

by:TheSloath
ID: 18797165
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
 
LVL 9

Expert Comment

by:TheSloath
ID: 18797183
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
 
LVL 75
ID: 18797199

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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:jamesdean666
ID: 18797232
None working....
0
 
LVL 9

Expert Comment

by:TheSloath
ID: 18797247
Can you post the SQL you are running?
0
 

Author Comment

by:jamesdean666
ID: 18797258
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
 
LVL 9

Accepted Solution

by:
TheSloath earned 500 total points
ID: 18797262
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
 
LVL 9

Expert Comment

by:TheSloath
ID: 18797285
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
 

Author Comment

by:jamesdean666
ID: 18797289
Rock and Roll oh mighty Sloath...

and many thanks!

JD
0
 
LVL 75
ID: 18797517
Basically what I said, lol ...
"Change your query field (or add this expression) to:
CInt([YourQueryFieldName])"

0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question