Solved

Query Criteria in MS Access

Posted on 2007-03-26
10
200 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
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

729 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