Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Query Criteria in MS Access

Posted on 2007-03-26
10
Medium Priority
?
203 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 2000 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

704 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