Solved

Query Criteria in MS Access

Posted on 2007-03-26
10
201 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses
Course of the Month8 days, 14 hours left to enroll

617 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