Forms :: Search / Filter By Multiple (optional) Textboxes On Form
Aug 1, 2013
I'm relatively new to MS Access (using MS Access 2013 but the db should work on 2010, too) and try to develop a database for an NGO I'm working in. I created almost all the tables (all that I need for now) and made the relationships.
However now I start to create forms and later reports for the actual user. The database will store information about clients and track consultations and assistance the NGO gives to them. There will be around 50.000 to 70.000 clients in the main table. Every client has a specific Individual ID and is member of a family which itself has another specific Group ID.
Now here is my problem: The User usually searches for the respective family by the Group ID. I implemented this with a search query using the ID number of a search text box. All done and no big problem.
But sometimes the ID number is not known so the user needs to search by name (First and Last Name). I use to different textboxes for this and it works in a similar way like the number search by query (Like "*" & [Forms]![frm_SearchIC]![txt_LName] & "*"). All still good However since most of the clients are actually from arabic speaking countries, converting the names into the Latin alphabet is bound to fail and produce a lot of misspellings. Therefore I added 2 more textboxes and 3 comboboxes for the user to give more information about the client and therefore make it easier to search for the person. I was able to produce a query which gives you the right result if you have ALL information at hand. However, this is not always the case.
1) But I cannot find a way to tell the query that if the a certain textbox or combobox is empty, it shall just "ignore" it and use the information at hand. I tried this in the query by adding in the criteria OR .... Is Null. This is alright for one or two textboxes but for the many I have, it seems to be too many different combinations for the criteria. It just worked with some fields but others always had to be filled in...
2) If no information is given at all, the database should inform the user that he needs to enter at least on field. If nothing is found the user should get a msgBox saying "No IC matches your criteria".
3) The results of the searches should be given out in another form where the user can pick the person from 1-to-many results.
I attached a sample database with sample data and reduced tables, fields, and entries ...
View Replies
ADVERTISEMENT
Aug 15, 2013
I have a form with two textboxes called: txtAptDep, txtAptArr.
On their right side there are two buttons (btn1, btn2) to open a form called frmAirportSearch. It allows to choose (from a listbox) the airport to be filled in those two textboxes.
Now, if I search for an airport pressing btn1, I want the airport to be returned into txtAptDep, while if I open the form by btn2, the airport choosen must be returned in the second textbox.
View 7 Replies
View Related
Feb 27, 2014
On the access form I have designed , I need to give optional search option to the users. I have the following search options on the form (screenshot attached):
From Date
To Date
Port
Vessel
Voyage
---------------------
The query is as follows:
SELECT dbo_VESSEL.VESSEL_NAME, dbo_VESSEL.VESSEL_CD, dbo_VESSEL.VOYAGE_NUM, dbo_VESSEL.PORT_CD, dbo_VESSEL.DEPART_ACTUAL_DT, dbo_VESSEL.DIVISION_CD
FROM dbo_VESSEL
WHERE (
(dbo_VESSEL.VESSEL_CD Like [Forms]![VESSDAT].[Form]![txtvessel]) OR
(dbo_VESSEL.VOYAGE_NUM Like [Forms]![VESSDAT].[Form]![txtvoyage]) OR
(dbo_VESSEL.PORT_CD Like [Forms]![VESSDAT].[Form]![txtport]) OR
(dbo_VESSEL.DEPART_ACTUAL_DT BETWEEN [Forms]![VESSDAT].[Form]![txtfromdept] And [Forms]![VESSDAT].[Form]![txttodept]));
----------------------
However, the form still prompts me to enter values for all the boxes and still doesnt show the correct data.
View 14 Replies
View Related
Sep 5, 2013
I have a form and I am not sure of the best way to populate the multiple fields on it.
The form consists of a textbox to select a date (txtDate) and then once this date is selected I want the multiple textboxes on the form to be populated from data I have in a table.
The name of each textbox is a concatenation of "txt" the number of the person which is a number from 1 to 4 and then a time eg for 12 Noon it would be 1200 so the textboxes are from txt10800 to txt42000 i.e. each person has textboxes from 8.00am to 8.00pm in half hour intervals.
I need to use two tables to populate these textboxes as in the main table there are codes that have a relationship to fields within another table that holds attributes for that code. (eg code 123 could equal Service A in the second Table but 123 is what is held on the main table) Also in the main table I have a calculated field that creates the name of the relevant textboxes on the form.
If there is a textbox that will not be populated as there is nothing in that time period then I would like this to be left blank or Null.
Am I best creating a query and binding the form to this query or to leave it unbounded and use a recordset within VBA
Also what is the best way of using either
I am using Access 2013...
View 5 Replies
View Related
Oct 27, 2013
I wanted to build a dynamic search form using text box instead of the common combo box type.
I found an example that used the combo box and the searching portion of the code is as followed:
Code:
If Nz(Me.txtID, "") > "" Then
If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
strFilter = strFilter & "CategoryID = '" & Me.txtID & "'"
bFilter = True
End If
How to insert (Like "*" & Me.txtID & "*") into the code to make the dynamic search using text box possible.
View 1 Replies
View Related
May 9, 2013
I'm trying to implement a macro to filter through accidents to see if my company has already been hired on a particular case.
In the first form the user enters the date, location, and last name of one of the drivers. Then they click a button which opens a new form as a datasheet with accidents that match ANY of the three controls. Evidently, I need a filter that contains some form of a series of OR operators.
However, whenever I try any variation of a button that opens the second form and then applies the filter, I always get no matches.
View 12 Replies
View Related
Dec 31, 2004
Im just starting to program in Access.. can somebody please help me with my problem?
Im trying to figure out how to search the table with the content of my textbox. (Ex. i input "A" in my textbox, it should display all the records of "A" like it's AGE, STATUS, etc.) it doesn't have to be case sensitive or exact because i have a primary key that doesn't accept duplicate.
Thanks to anyone who'll help
View 3 Replies
View Related
Oct 21, 2013
I've got a form called "Main Menu" and i'm in the process of implementing a search form into it. So far I have three textboxes called TBFirstName, TBSurname and TBCentre these are unbound. I also have a search button.
This button runs the query which runs the search, the sql for this query is below.
Code:
SELECT Staff.[Employee ID] AS [Staff_Employee ID], Staff.Forename,
Staff.Surname, Staff.Sex, Staff.[Job Role], Staff.[Start Date], Staff.[Leave Date], Staff.Centre,
Staff.Hours, Qualifications.[Employee ID] AS [Qualifications_Employee ID],
Qualifications.[Level 2 Maths], Qualifications.[Level 2 English], Qualifications.[Level 3 Support Maths],
[Code] ....
Whenever I run this query it just returns all the records instead of the search criteria. Where am I going wrong?
View 14 Replies
View Related
Jun 16, 2015
I have a form that has two different option button frames and 1 text box that I would like my users to be able to use to search/filter for specific records. I chose a form because a table does not work as a user-friendly interface for the data that is being stored. The first option button frame allows the user to filter by Month and is named [FilterDate].The second option button frame allows the user to filter by incomplete and complete records and is named [FilterOption].
The text box is used as a search box and is named [txtsearch].I am able to successfully create macros to apply filters for each of the above options; however, each time one of the options is updated, it removes the previous filter. What I need is creating a macro (preferrably) or vba code (if macro is not possible) so that my users can first pick a month (as this will always be the first thing they do when opening the form), then pick complete or incomplete records within that month if desired. The search box then would only filter records that meet the first two criteria (month and complete/ incomplete).
View 5 Replies
View Related
Jan 9, 2014
Access 2003
Trying to filter a form based on a field with wildcard. My form has a txtCustFilter control where a customer's name can be entered in part or whole. The Customer's name is in PCCustomerName
This code works but, I'd like to make it case insensitive
Dim strFilter As String
strFilter = "[PCCUSTOMERNAME] LIKE ""*" & Me.txtCustFilter & "*"""
Me.Filter = strFilter
Me.FilterOn = True
View 9 Replies
View Related
Aug 12, 2015
I keeping getting the error "Invalid use of Null" when i call my email function using optional parameters.
Code:
Email Me.ProjectID.Column(1), _
Me.ProjectAddress, _
Me.TaskDescID.Column(1), _
Me.TaskDescExt, _
strStatus, _
Me.TaskStatusID.Column(1), _
[Code] .....
View 4 Replies
View Related
Mar 12, 2014
I want to open a filter form with mulitple conditions.one of them is a date condition.
I'm using access 2013 and the code below:
Code:
DoCmd.OpenForm "Edit_Mission", acNormal, , "[Report_Date]= " & Me.Date & " And [Supporter_Name]='" & Me.Supporter & "'"
it's opens the form but with no data. I also tried the # and it's still didn't work.
View 1 Replies
View Related
Jun 30, 2015
I am fairly new to Access and am having some trouble creating a search form. I have numerous tables, which contain information regarding clients and contracts, that are all connected by relationships. I would like to create a form in which I can type in the name of the client and pull up all the information regarding it from the various tables (i.e. when it was signed, the amount, and etc).
View 2 Replies
View Related
Mar 6, 2013
I have a column that has a bunch of keywords they are separated by comma... so for each row of that column it will have a few different keywords example: lake superior, river, mountain, lake wollongong
I know its a bit of a nono with databases to have columns with comma separated text.. well so i read somewhere anyway but the document i have been handed to work has hundreds of rows in this column with up to 14 keyword entries.
I have a form that searches through Item's names based on 2 keyword boxes.
Here's the criteria in my query:
Is Null Or Like "*" & [Forms]![frmItemView]![SearchPhrase1] & "*" And Like "*" & [Forms]![frmItemView]![SearchPhrase2] & "*"
This will show all records when both keywords are blank, and filter records using the 2 keywords otherwise.
I have a form with two text boxes and have set the correct values as outlined in Evans post. I then have the query set to run via a button. I run it but it will only give back records for the entry i have put in the 1st text box. This would work well for me otherwise... mine is like this:
Is NULL or Like "*" & [Forms]![Searchtable]![Key1] & "*" And Like "*" & [Forms]![Searchtable]![Key2] & "*"
Searchtable being my search form
key1 being my first text box entry
key2 being my second text box entry
View 1 Replies
View Related
Oct 1, 2006
I've had a look around this forum and haven't seen anything really matching my needs, but please feel free to point me in the right direction.
What I am trying to do (using macros & queries - very new to SQL / VBA) is filter by multiple fields, but also allow the user to choose to filter by different combinations of fields - such as searching for first name and suburb, or salesperson and customer source, or first name and salesperson, or suburb and customer source, or three options or one, etc etc.
There will only be a certain number of search fields, probably five or six, but how do I get the query to recognise that if one of the search boxes is blank, it should allow all records in that field and only search by the other criteria, but if all the search boxes are filled in, the results must match all the criteria?
Does this make any sense?
PS - I am planning to use a command button as an "Apply Filter" so that the filter only updates after the user has specified all the options they want.
View 1 Replies
View Related
Jun 26, 2013
I am attempting to create a search form where a user can search by either employee name or company name. I have 5 tables to use. Is there a simple way of creating a search method for this? I would like to be able to have the user type in a name and click a button that says search with any records matching the search come up. However, I could definitely use a method where they type it in a box and it finds it as the person types.
How to search from all five tables at the same time.
View 3 Replies
View Related
Dec 16, 2013
I am having some difficulty putting togather a small database (attached).
Basically I want to be able to search for mutiple words and get the results
There are 3 main columns in the main table
1) Shop
2) Part Number
3) Part Type
Problem 1:I have created a lookup table (with check boxes) for ComboBoxes in a form but I do not know how to connect it so that when I click on the comobo box it gives me the all different (Shop numbers/part number/Part types) to select from.
Problem 2:Once I have made my selection I want to search and run query to show me the results. I know that once multiple items are selected in a combo box they are separated by commas/space, so the search must take that in account.
Other nice to have
1) If Shop 1 is selected then only the parts which were sold from shop 1 to appear in the Part number Box & visavera & same goes for Part type.
2) Ability to do a wildcard search so if I Enter "A" in part number I can see all the resealts
View 12 Replies
View Related
Jan 9, 2014
I am creating a database of bird banding and breeding records for the refuge where I currently intern. To do this, I have created the following tables:
Adult Banding Records
Nestling Banding Records
Historical Banding Records
The specific issue:
I have already created forms for entering data into these tables, but now I would like to create a "search" form that will use the primary key (which is always the band number of the individual bird, across all tables) to search ALL the tables and pull up all the information on that record. This will be convenient when we recapture a bird that is already banded, so we can look up their information in our database. Also, since I plan to add many more tables, it would be nice to not have to search each table individually.
View 10 Replies
View Related
May 10, 2006
I am using this criteria with my calender on my form...
>=([Forms]![frmReport]![txtFrom]) And <=([Forms]![frmReport]![txtTo])
but I have 2 other date fields that I would like to query, so how do I go about making this optional?
Normally with a simple reference like:
Like([Forms]![frmReport]![txtFrom]) & "*"
with the form control's default value as * I tried working with that configuration but either it is not possible or I have the wrong syntax.
I'm open to a VB solution if anyone has one?
Note: I would only be using one of the date fields at any given time
thanks
View 2 Replies
View Related
Jan 26, 2015
I have a form that is showing data from 1 table. That table has 12 different fields on it and I want to be able to filter based on selections I make in a combo box in the header of the form. The filter string must be dynamic enough to allow filtering based on 1 criteria selected, or multiple criteria selected. For example:
If I have values in filter fields 3, 5, and 9 I'd want the filter string to be created as follows:
"...WHERE field3 = field3filter.value AND field5 = field5filter.value AND field9 = field9filter.value"
If I have values in only field 7, I'd want th efilter string to be created as follows:
"...WHERE field7 = field7filter.value"
And so on and so on.
I have created some filters before but all of the different VBA syntaxes I'm using seem to come up short.
View 5 Replies
View Related
Jul 24, 2015
I haven't been able to find if there is a maximum length of text within a textbox.
I have a form on a club database, and one of the textboxes has the details of their photographic experience. I have one person who is a very experienced worker and I need to add more details in the textbox. Currently there are 29 characters, including spaces in the box, but it won't allow me to add any more. I have set the width of the box on the form to a wider setting, but this makes no difference. What can I do?
View 5 Replies
View Related
May 24, 2013
I have a form with checkboxes, indicating fields which can be selected for a query. The strSQL variable works fine, and I can use QUERYDEF to create a query from the variable.
I wonder if it is possible to generate a form to display only the fields chosen - i.e. I don't want a general form with all fields, with only the chosen ones displaying data. Instead, if I select only 5 of the 10 fields, I want the form only to show those 5 fields.
Creating textboxes for each field, and using the .visible property, but then I would have lots of gaps on the form.
I hoped it might be possible to use a loop with a value set something like
For i = 1 to fieldcount
textbox(i).recordsource = selectedfld(i)
'somehow know where to place the textbox!?!!
Next
View 14 Replies
View Related
Dec 12, 2013
I have a form to input info into a table how can I combine 2 txtboxes to one column separated by ", "
Example:
textboxes:
txtboxFirstName txtboxLastName
output:
Last, First
into the "Name" Field in my table
View 1 Replies
View Related
Feb 27, 2008
Hi folks. I have started getting back into Access at work. It's been at least 3 or 4 years. The company I am building this db for is using '97.
I am trying to build a form with a search button and a text box to type a search string.
The form also has a subform which will display or filter the records based on the search criteria
I have tried to adapt "themightydude's" code to work, but i'm not getting anywhere...
I am truly trying to get back into the programming mindset, but have a "writer's block" at this moment.
I really want to understand how VB works. (& Sadly, I know it's one of the easiest languages to understand...)
here's what i have so far.
Private Sub Search_Click()
Dim GCriteria As String
If Len(cboSearchBy) = 0 Or IsNull(cboSearchBy) = True Then
MsgBox "You must select a field to search."
ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
MsgBox "You must enter a search string."
Else
'Generate search criteria
GCriteria = cboSearchBy.Value & " LIKE '*" & txtSearchString & "*'"
'Filter frmCustomers based on search criteria
Form_GlobalSearch.GlobalSearchSub.Form.RecordSourc e = "select * from projects where " & GCriteria
Form_GlobalSearch.Caption = "projects (" & cboSearchBy.Value & " contains '*" & txtSearchString & "*')"
MsgBox "Results have been filtered."
End If
End Sub
I have also attached a picture of the search form and a copy of the db for reference.
Sorry, I know this code is probably WAY off. Any help is greatly appreciated.
Thank you all for your help!
View 2 Replies
View Related
Feb 28, 2006
ok i would like to create a very specific search form. the form has many fields but they all belong to one simple table.. Enquiries.
i know this table isnt normalised but this section is separate from the database and i need this certain filter feature on the enquiry form i have made.
a screen of the form can be found here.
http://bb.1asphost.com/antonyx6/enqform.jpg
the top 3 combos contain all the records for that particular field.. the first bit of info i need is how to alter the statement for the combo lists to ignore values with no entries..this is because not every stored enquiry will contain an enquirer's forname.. is this correct for my Forname combo....?
SELECT zEnquiries.Enquiry_ID, zEnquiries.Enq_Forname FROM zEnquiries WHERE zEnquiries.Enq_Forname is NOT NULL;
i will take things one step at a time.. so if i can clear this up first i will describe what i want to happen next if thats ok..
thank you.
View 2 Replies
View Related
Jul 9, 2013
I have a form with two textboxes that get their values from two different queries that counts records from table. If textbox1.value equals texbox2.value the textbox2.value back ground colour is green. If they are not equal textbox2.value goes red. Itried with using conditional formatting, but it doesn't work all the time as the form is not updating when it is opened.
View 4 Replies
View Related
