 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / July 2005How to search with a ranged criteria?
Hi again, I know I must sound like a broken record but I still have one question How can I search a field for a range of numbers. Now I know most of you will want me to use queries but my boss wants it in VB so I have little choice. Does anyone know a code that will work. preferably one that will require the user to enter only one number, but I am ok with the user inputing the range ie from # to #. Thanks! James What do you want to return? If you only want one record, the DLookup function might do it, but I doubt it. I don't think you are going to be able to do this without a query. You don't have to construct a query, you can code it in VBA as an SQL call. > Hi again, > [quoted text clipped - 8 lines] > Thanks! > James Hi Klatuu, DLookup wont work, i need it to return multiple records. an SQL statement might work but I would like it to open a particular form I have created for the table. If I remember correctly the other problem you had helped me with would only open a table or query. If you can help me with this I would apreciate it. Thanks, James > What do you want to return? If you only want one record, the DLookup > function might do it, but I doubt it. I don't think you are going to be able [quoted text clipped - 13 lines] > > Thanks! > > James James Sounds like a parameterized query to me. SELECT foo, bar FROM FooBar WHERE foo Between [Enter Min foo] And [Enter Max Foo] ORDER BY foo, bar If you can supply the DDL and some test data along with a good specification, I will be happy write the code for you. Ron W > Hi again, > [quoted text clipped - 8 lines] > Thanks! > James Ok, here is some data The form name is tblPropellant Query, which is the same name as the query it was made from. The Field name to search is ITD, and the data entry field names are MinDTI and MaxDTI, and so there is no confusion the field names are supposed to be the flip of ITD if you need any other data just ask :) Thanks, James > James > [quoted text clipped - 22 lines] > > Thanks! > > James James You are kinda' light on the info provided so I have no choice but to make some assumptions. * I assume the form is a continuous form * I assume the two text boxes are in the forms header and are not bound to anything * I assume there is a button named cmdFilter also in the forms header * I assume the that ITD is some kind of a number Then in the code behind the form in the click event of the cmdFilter button add the following. Private Sub cmdFilter_Click() ' Purpose Filter the records to include only the records ' whose ITD value is between MinDTI and MaxDTI Me.filter = "ITD between " & Nz(MinDTI.Value, 0) _ & " and " & Nz(MaxDTI.Value, 0) Me.FilterOn = True End Sub User enters some values in the min and max text boxes, pushes the button, and the form filters OUT all of the records that are not between the min and max values. If either text box is null then the Nz() function supplies a 0 to the filter. If the user enters a non numeric value it will generate an error, so you need to insure the users only enter values that are appropriate for the data you are manipulating before you apply the filter. If any of my assumptions are not correct then this solution is a non solution, sorry:-( Watch for Newsreader wrap and good luck with your project. Ron W > Ok, here is some data > [quoted text clipped - 35 lines] > > > Thanks! > > > James Ron, sorry about being light on the info, I actualy have the text boxes in a search form that I want to use to open the data form for the search criteria. so would I change Me.Filter to the form I want to opens Name.Filter? Also I am getting an object required runtime error for the first line of code Me.Filter=... any ideas? If there is any information that would make this easier for you just tell me where to find it and I will send it to you, I am still fairly new at this. Thanks, James > James > [quoted text clipped - 76 lines] > > > > Thanks! > > > > James Then open the form with the Where Condition set same as the filter in my previous post. Eg. DoCmd.OpenForm "tblPropellant Query",,,"ITD between " & Nz(MinDTI.Value, 0) & " and " & Nz(MaxDTI.Value, 0) The above line should all be on one line. Watch for newsreader wrap This time the assumptions here are: * I assume the two text boxes are on the form that is opening "tblPropellant Query" form * I assume the name of the form you are opening is "tblPropellant Query" I still think you should stop writing code for a day or two and start looking at solutions written by others and reading books. In the end you will be far more productive. Before you knew how to read it would have been fruitless for your parents to want you the write the next great novel. Try to master the basics first. Also try to use names that are more appropriate for the kinds of objects you are working with. "tblPropellant Query" could be the worst name I have seen for a form. As long as you are gonna' go into study and learn mode, spend a few minutes at http://www.xoc.net/standards/rvbanc.asp to get a solid foundation on Naming Conventions. Ron W > Ron, > [quoted text clipped - 91 lines] > > > > > Thanks! > > > > > James Ron, when I begin the search it asks for ITD in a user input box then no matter what I enter it opens the form for all records. My guess is it has something to do with either the quotes or the datatype, but I am unsure how to check either of them. Thanks, James > Then open the form with the Where Condition set same as the filter in my > previous post. Eg. [quoted text clipped - 136 lines] > > > > > > Thanks! > > > > > > James James Please send the Sql statement that is the Query that is the record source of the form "tblPropellant Query" Ron W > Ron, > when I begin the search it asks for ITD in a user input box then no matter [quoted text clipped - 145 lines] > > > > > > > Thanks! > > > > > > > James Ron, I found the mistake, I reversed the names of the field to be searched and the criteria fields, ITD & DTI, I'm sorry about that, it looks like it works now. Thank you very much, James > James > [quoted text clipped - 174 lines] > > > > > > > > Thanks! > > > > > > > > James |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.