Searching For Value Range On A Custom Search Form
Jun 9, 2014
I built a custom search form that uses unbound text boxes to set the criteria for a query. For example, I have an unbound text box, LastName on my search form. Then in the query, I set the criteria to Like "*"&[Forms]![SearchForm]![LastName]&"*". This returns all records if the text box is left blank or returns those records that match the text box if it isn't.
I would like to do the same thing but instead of searching a text field, I want to search a number field. And I want to search for a range of values aka >10 instead of just a single value. I still need the query to return all records if the text box is left blank.
View Replies
ADVERTISEMENT
Jun 3, 2014
The database is a patient database used for tracking Inpatient data and Handover (or Handoffs for those in the US). It has been merrily ticking over since December and has accrued several thousand records. To get access to this data in new and interesting ways Im trying evolve my rudimentary search function which currently only works for diagnosis, name and consultant (in otherword text fields).
Thus I have an unbound form that has various input boxes on it. I have borrowed some code to power the form and have replaced with own fields:
Code:
Dim criteria As String
Looks for input text and adds it to the final criteria to power the search list at the end based on field in this case txtDiagnosis
If Not IsNull(Me.txtDiagnosis) Then
If criteria <> "" Then criteria criteria & " and "
criteria = criteria & "txtDiagnosis like " & Chr(34) & "*" & Me.txtDiagnosis & "*" & Chr(34)
End If
[code]...
ISSUE 1:
I am struggling with the date search function. It doesn't work. Whether it is prudent to search for either a admitted date or a discharged date?
ISSUE 2:
I have a check box for "complications" that I'd like to be included on in the search.
View 11 Replies
View Related
Jun 9, 2014
I've created a custom search form where using unbound text boxes and combo boxes I enter the criteria for a query. I would like to enhance my form to allow "and" and "or" searches on a field. For example, a product on my database has multiple features. I would like to search for products who have "feature1 and feature 2" or "feature1 or feature2". {Note: The table, Product is connected to table, Features using table, LineItem_ProdFeature where there are multiple records in Features for one record in Product}
Right now, the search form has a combo box that lists all product features. The user selects a feature and runs the query. All products with that feature are listed in the query.
It would be so great if the user could choose multiple features from the combo box. Then the query would return all products that have all of the selected features ("and" search). It would be just awesome if the user could select multiple features, and specify if they want an "and" or "or" search of those features.
View 14 Replies
View Related
Jan 28, 2015
I have made a database for work and is fully functional, but theres one thing I want to add but cant get my head around how to do it.
I have created a Form called 'Filtered Search', on the form it has multiple combo boxes for 'Auditors' 'Area' 'Status' and 2 text boxes for date range.
I want to be able to set what filters I want, and for the query to ignore any fields with no information selected/inputted (i.e. I want to see all records raised by "Mr Smith" (Auditor) that are still 'Active' (Status) in all areas at any time).
Names of items;
Table = 'Incidents'
Form = 'Filtered Search'
Report = 'Filtered Report'
Auditor = 'Combo7'
Status = 'Combo156'
Area = 'Combo5'
Date Range From = 'Text161'
Date Range To = 'Text163'
View 5 Replies
View Related
Jul 11, 2015
I have created a database based off of 1 table that holds all of my data. I need to create a form that queries off of the following columns from my table.
Acceptance Date
Sales Rep Name
Sales Region
I want to make a form which searches between a beginning and end date range, Sales Rep Name & Sales Region that can be pulled from these 3 queried selections all from the same form.
View 2 Replies
View Related
May 25, 2012
I'm trying to create a combo list box in a form that has all 12 months (January, February......etc,) listed in rows, and depending on the selection will bring up only that particular month within a range of dates from a table. I don't want it to look at the day or the year, but only the month.
Example: I select January from the drop down list in my combo box and my form will display all records with dates that are in January regardless of month or year.
View 4 Replies
View Related
Aug 9, 2005
Hi,
Im a web designer by trade and know pc's fairly well, im trying to help a friend who is pc illiterate build a database.
It would help him out and be some practice for me (always wanted to have a bash at access but never had a reason to do so)
Im totally new to Access and im trying to do the following.
He is an estate agent and wants a database where he can enter his prospective clients looking for commercial properties to rent, one of their criteria will be the size range of the property they want. Then when he gets a new property on his books he wants to be able to search the client list by the size of the property and display the results of any clients that the properties size falls within their required size range (phew hope that makes sense)
In an ideal world hed like the results to include addresses so he can mail merge the results and send out letters to any clients who might be interested.
So, ive managed to create a table of all his client details but now im stuck on how the query should work, ive read the help files in access (which seem to be written in martian) and im just not sure how i should try and do this. I started to create a parameter query but ive gotten very confused with all the options.
I can see that i could put fields in my table of Min Size and Max size but im not sure how to design a query that searches a single value between these 2 fields
Ive had a look through this forum but cant see anything that covers this, im hoping someone might be able to point me in the right direction, either with some advice or a link to some online help that might help me understand what i need to do.
Thanks for taking the time to read this post, apologies if its in the wrong place or a totally noob question.
Any help would be very much appreciated
Many thanks
Dave
View 5 Replies
View Related
Jun 3, 2005
I am currently creating a new part of my database which will enable users to store data for Storage Job which we do. the form has a number of fields called "pallet ref1 / pallet ref2" etc. now when the customer phones up and gives the pallet reference number, the user will need to search these fields to find out where in the warehouse the pallets are stored. But i want to create a search that will only search fields "pallet ref1 / pallet ref2" so that it will not have to search all the feilds within each record.
ne help would b appreciated
View 3 Replies
View Related
May 18, 2005
I support a database for some users who don't find the built in search function (ctl+f) to be useful enough.
The main data entry form of the database has fields for subdivision, lot number and address, any of which they may use to find the record they want. They're requesting that I add two types for searching:
* select sub and/or lot number as search criteria and have the selected record populate the screen
* type in a freeform address and have the record populate the screen
I'm debating about what the best approach would be to do this. Should I have a command button which opens a pop up form with the fields? If so, what is the code I will need to take the data selected/entered on the popup, run a query and then populate the underlying form? I think I need to pass parameters but I'm inexperienced at this so I need some guidance.
Also thinking that if I do create a popup form that I'll use it in the open event on the data entry form as well.
Thanks.
:confused:
View 3 Replies
View Related
Mar 2, 2005
Scenario: The code below allows me to enter a date range in an unbound form which then opens another form based on the date range. If I enter only one of the dates (the beginning or the end) and not the other. I have a message box display asking for the other date to be entered.
Problem: The message box displays correctly but the secondary form still opens because my open form code is separate from the verify dates code. I can see the problem but I can't think how to fix it.
Desired Results: After the message box appears and I click OK on the message. I want the secondary form not to open at all.
***********CODE SNIPPIT************
'Build the wherecondition to use in the OpenObject method.
If Not IsNull(txtTxPrID) Then
strWhere = strWhere & " AND TxPrID = " & txtTxPrID
End If
If Not IsNull(txtSSN) Then
strWhere = strWhere & " AND SSN Like " & "'*" & txtSSN & "*'"
End If
If Not IsNull(txtAddDateBegin) And Not IsNull(txtAddDateEnd) Then
strWhere = strWhere & " AND AddDate between #" & txtAddDateBegin & "# and #" & txtAddDateEnd & "#"
Else
DoCmd.CancelEvent
Beep
MsgBox "Please enter both dates", vbInformation, "Incomplete Search Criteria"
End If
'Open Form based on the contents of strSQL.
stDocName = "empfrmTaxPayerInfo"
DoCmd.OpenForm stDocName, , , strSQL
***********END CODE SNIPPIT************
Thanks in advance
Shel
View 4 Replies
View Related
Jun 18, 2015
I have utilised the code on John Big Booty's thread titled Dynamically search multiple fields (Thread #188663) to create a dynamic search feature
It works great however I would like to incorporate a date range filter into the results displayed in the ListBox.
Therefore the results would be dynamically filtered only if they meet the required date range.
The Date range data is on the form in a txtdatefrom and txtdateto textbox.
I have tried to adding a the ReleaseDate field with the following criteria to the query:
Between [forms]![frmSearch]![txtdatefrom] And [Forms]![frmSearch]![txtdateto]
however this has no impact at all.
How would I incorporate this date range into the query or VB Code?
View 6 Replies
View Related
Jun 13, 2015
I have a form with a subform which searches the database using multiple criteria using Text Boxes and a search button. One of which is a date range which is entered into 2 text boxes(SDTxt and EDTxt). At first glance the code works. If I enter 03/05/2015 in SDText and say 24/05/2015 in EDTxt and hit the search button(SearchBtn) the correct records are shown in the subform (Office Subform). But if I enter 05/06/2015 in SDTxt and 13/06/2015 into EDTxt not only does it show the records between the 2 dates but also all records from May. I have put on a cut down version of the code which just shows the date range search.
Code:
Private Sub SearchBtn_Click()
Dim strWhere As Strin
If IsDate(.SDTxt.Value) And IsDate(.EDTxt.Value) Then
strWhere = strWhere & _
[code]....
View 3 Replies
View Related
Nov 24, 2013
I am building Access (2013) database for 100+ employees to keep track of their expiry dates of visa/passport/work permits, for that to work I need to be able to search between date range to generate a report to work on. please check out the image attached as it will explain better where I am stuck, with current settings access keeps giving me empty query table as I think it searches for exact same result in all 3 fields at the same time. But I want it to search between all 3 fields and display if any of the 3 fields falls into that range.
View 3 Replies
View Related
Apr 15, 2013
what I want to do is make a button to search range of columns in data table with data type Yes/no and display the results if the value is yes
View 9 Replies
View Related
Jul 21, 2005
http://lemmikki.uw.hu/promotion.JPG
Okay, this is how my form looks. I have thousands of records, but in a special way. I would like to make a search having the email, when and what together. The table is like this:
email1@xyz.com 20050712 E0001
email2@xyz.com 20050712 E0001
email3@xyz.com 20050712 E0001
email2@xyz.com 20050712 E0002
email3@xyz.com 20050712 E0002
email1@xyz.com 20050720 E0003
email2@xyz.com 20050720 E0003
email3@xyz.com 20050720 E0003
email4@xyz.com 20050720 E0003
email1@xyz.com 20050720 E0004
email4@xyz.com 20050720 E0004
Okay, there's a key for finding the records, but searching by it is quite difficult. Searching by these 3 data would make it easier. But it's quite difficult for me. I would like to have 3 lists (on the picture I have 2 so far) and a button that I can press and the record would come out...
Can someone help me?
View 4 Replies
View Related
Dec 20, 2005
Hi,
On my form i have a text box (txt_search) and a search button (cmd_search). Now when the button is pressed i want it to go to another form (that bit is done) and display the search results (the bit that is hard).
How can i get it so that when i click the button. It searches some predefined fields with the text in the txt_search once the button is clicked.
Thanks
k0r54
View 1 Replies
View Related
Jul 26, 2006
I was looking for help to find out how i would put a search facility on a form
Ive written a query with a parameter where you input a persons name in order to find it in a table
the porblem is when i base a from on this query it brings up the parameter asking me to enter the name, the query works fine it does what i want it to do.
i want to be able to open the form and if i need the facility to enter a name an d bring the details up in a sub form
any ideas?
View 2 Replies
View Related
Jun 11, 2013
I have used one of the members' code which was posted in a tread before to create a search box in a form. This code works perfectly when the form itself is open. Otherwise, when I put this searching form along with other forms in one navigation form, I get the following error: "Enter Parameter Value: Forms!FRM_SearchMulti!SrchText"...The code that was used for the Search box is as following:
Private Sub SearchFor_Change()
'Create a string (text) variable
Dim vSearchString As String
'Populate the string variable with the text entered in the Text Box SearchFor
vSearchString = SearchFor.Text
[code]....
View 7 Replies
View Related
Apr 14, 2006
Hi all. This is one has been troubling me for quite a while and once more I seek for solution to the right (hope so) place.
I have imported from a Excel a table and based on that table I created a form. But when I try to add a combo box on the form header in order to search for a particular record to display on screen the error on the attachment occurs. I didn't add even a single character of code, and the error message seems weird.
I tried doing the same thing on another pc (an older one) and it is working fine...I've added search via combo boxes in the past without any problems but this time something isn't going right.
Any clues?
View 1 Replies
View Related
Apr 20, 2006
Hi,
Me and my friend are making a database for a DVD rentals business and we're having trouble with searching within a form. We want to be able to perform a search in a form by clicking on a button within the form rather than using a query or filter, and we want the results to be displayed within the form in the available fields.
Many thanks!
View 1 Replies
View Related
Apr 21, 2008
I have a form I am using to search for records based on any number of criteria. The one I am having a problem with is a model number search. I would like to search based on a from value and a to value.......for instance........let's say you had the following items:
bird
birth
bill
birdbath
bite
bitter
If I want to search for values from bir to birt, I would get bird, birdbath and birth. How can I code my query value to look for values between two text boxes in a form?
View 1 Replies
View Related
Feb 23, 2005
I have a parent table called patient info. and a child table called fills. The database in the future will become very large, and will be difficult to find a particular patient in the parent table to update fills in the child table. How can I search through the parent table using a form for easy access to a particular patient so that I can update fills?
Thanks for those who help!
View 1 Replies
View Related
Jan 4, 2015
I have a table containing customer details and a form which allows you to view them like records.how i could make something that enables me to search their fullname that would take me straight to that record.
View 2 Replies
View Related
Aug 5, 2014
I have a search form that uses a query to show results of a search, but everytime I press search everything comes up even though I have entered search parameters, even though my search requeries every time and the search used to work before I added new records today. Also when I press the query alone on the navigation pane it asks me for the parameters and then it actually works but it won't when I use my form.
View 2 Replies
View Related
Jul 10, 2012
i need to create a system that can search data using keyword.so i want to create search button that i will create it at form. currently i have 1 table and in that table i have 10 fields which are project_name, doc_ref_no, doc_title, volume, book_no, author, doc_status, box_barcode, filling_location, doc_availability.
i have create 2 forms, one form for user to add new record and another one is to search record. the user can search by enter project name, doc title, volume and box barcode. for project name and volume, i use combo box and for doc title and box barcode i'm using text box.
i want to create search function that can detect from user keyword and even user did not fill in all the form, the system still can search the record.
View 5 Replies
View Related
Jul 19, 2005
I have a form that assigns an Auto Reference Number to each record. Now the records are later edited. Is there a way that I can have the user enter the Reference number and come directly to that particular record that needs to be changed in the form. Like a dialog box that asks Enter Reference Number or something.
View 2 Replies
View Related
