Multiple Text Search On One Column To Filter Records?

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 Replies


ADVERTISEMENT

Forms :: Dynamic Search - Subform Filter With Multiple Text Box

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

Filter Using Multiple / Multi-Column List Box

Sep 17, 2012

I'm not familiar with listbox yet and i want to filter my form using it.

I have two separate listboxes which display "category" & "type" data from the same table.

Here is the situation i wanted for my listbox.

1. Select one data from "category" listbox.
2. Then it will automatically filter data from "type" listbox or it will list all related "type" data corresponding to "category" data.
3. Then select one data from "type" listbox and it will filter all related data on the form/sub-form.

Is it possible to do that way? Can i do it on multicolumn listbox instead of using 2 listbox?

View 1 Replies View Related

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 6 Replies View Related

Forms :: Macro To Apply Filter - Search Multiple Fields

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

Forms :: Search BETWEEN - Filter Query Showing All Records

Mar 14, 2013

I have created a form for a table which contains ~600 movies and their name, genre, rating, director, year it was made, and length (min).I need to be able to enter numbers into the two Year boxes, and then it filters the movies in my database and only shows me records from between those two years. The years in my database are just in one column in the format of: XXXX e.g. 1996 etc..I've tried the code:

Code:

Private Sub Year2_AfterUpdate()
Me.Filter = "[Year] BETWEEN" & Me.Year1 & "AND" & Me.Year2
Me.Filteron = True
Debug.Print "[Year] BETWEEN" & Me.Year1 & "AND" & Me.Year2
End Sub

Year1 and Year2 are the boxes Year: and To: respectively. [Year] just being the column name which contains all my years.When I try to run my query it shows me my records, but it shows me all of them! It doesn't filter it at all!

View 4 Replies View Related

Multiple Filter With Unbound Text Boxes

May 21, 2015

The layout: I have form1 listed in continuous view. I have about 10 fields being listed. I have unbound text boxes for each field in the form header designated as a filter for each field.

Ideal world: Have each filter update records as you type. But I would also like for a "cascade" effect on the filters as well. Being that I can type in a few letters in FilterField1, and type in a few letters in FilterField2 and it would only display the records where the criteria is met for both filters.

What I'm not looking for: Only applying 1 filter at a time for 1 field. I have this setup now, but would like it to be more versatile allowing several fields to be filtered at once.

View 2 Replies View Related

Modules & VBA :: Enter A Keyword Or Phrase And Search 3 Memo Fields And Filter Records Found

Nov 7, 2013

I am trying to provide the user a custom search feature. They want to enter a keyword or phrase and search 3 memo fields and filter the form base on the records found. they also want to be able to search the whole phrase or any part of the phrase.

I have a like expression for any part of the phrase but I when I set it up for whole phrase it doesn't work. Even if I run a simple query and use

For example: There's an acronym the user is looking for : ACA

If I set my query up like this: [field1] like "*ACA*" or [field2] like "*ACA*" or [field3] like "*ACA*"

it not only finds records with that acronym but it also finds records where that combination is found in a word, for instance vacate.

How can I set up my query to find the whole word?

View 3 Replies View Related

Queries :: UPDATE Multiple Records In Same Column

Jan 16, 2015

I am trying to run below to update multiple records in the same column and get error message saying characters found after end of SQL statement. I tried to remove ; but then get a syntax error.

Code:
UPDATE [tblMonthly] SET [Date] = #20130701# where [File] = 'A';
UPDATE [tblMonthly] SET [Date] = #20130801# where [File] = 'B';

View 4 Replies View Related

Modules & VBA :: Inserting Multiple Records From Multiple Unbound Text Boxes

May 6, 2014

I have a form with 15 unbound text boxes (daily temperatures) and what I am trying to do after entering the temperatures into the text boxes the user clicks an add button which will add 15 new records into the temperature table

the code I have started off with is

Code:

CurrentDb.Execute "INSERT INTO ColdTemperatures (ProductID, ColdTempDate, Temperature) VALUES (" & Lettuce & ", #" & Me.RealTime & "#, " & Me.Lettuce & ")"

which adds 1 successfully however if i repeat the code above for all 15 this Im assumming will create a potential bottleneck and slow the system down

is it possible to add all 15 records at once? do you think Im going at this the right way

View 5 Replies View Related

Search Multiple Text Fields In A Table From A Form

Aug 8, 2006

Please bare with me as I am an Absolute Access VB code novice . I have a Form in my Access DB and I found the Following code on this site and placed it behind my search button :

Private Sub SearchButton_Click()
DoCmd.OpenReport "All users", acViewPreview, , "[AUP].[First Name] = '" & Me.[Text34] & "'"

End Sub

The Search works great but only searches on the 1 Field "First Name" . How would I expand the VB code to include another 2 fields within my table . ( the other 2 fields I want to serach appear on the Form ) .

View 4 Replies View Related

Forms :: Using Single Search Button For Multiple Text Box

Feb 22, 2014

I am creating search boxes within that searches customer by lastname, phone number, address and suburb. Will I be able to use one search button for all of them or do they have to be individual buttons?

View 4 Replies View Related

General :: How To Filter Records Using Multiple Combo Boxes

Jun 29, 2014

I have a table with the following 5 fields. (Service Type), (Valve Name),(Size),(Rating),(Description).

I want to do two thing:

First: I want to select the required information from the first 4 fields using combo boxes and get the last field (description) based on the selected 4 fields. In other words, i want the record to be filtered using first 4 fields to give me the last field info.

Second: I want to store the filtered record (all 5 fields) in another table.

View 6 Replies View Related

Modules & VBA :: Text Field In A Form Updated Multiple Times When Filter Is Applied?

Sep 9, 2013

In my Access 2010 database there is a form with a large number of checkboxes enabling me to select/deselect certain record details.

Each click on one of the checkboxes triggers a VBA routine setting true/false values in one of several tables linked to the main table.

A query over all records is filtered by the true/false values of the linked tables and the resulting set of records is displayed in the (continuous) form.

The header of the form contains a text field with the value "=GetRecCount()" displaying (correctly, but randomly fast) the number of records selected:

Code:
Function GetRecCount() As String
Dim NumRecs As Long
Dim rs As Object
On Error Resume Next
Set rs = Forms![Super Search].RecordsetClone
rs.MoveLast
NumRecs = rs.RecordCount
Set rs = Nothing
GetRecCount = Format(NumRecs, "##,###")
End Function

Now, the funny thing is that each click on one of the boxes results in 1 call of the VBA routine setting table values, but 21 calls (!) of the text field "=GetRecCount()" in the form. I have established this by incrementing a public variable at each pass.

Why a field in a form is updated 21 times when a filter is applied?

View 4 Replies View Related

Queries :: Search Query Using Multiple Combo And Text Boxes

Aug 13, 2013

I have a table with all information on it, that is input via various forms, I then have different queries pulling information from all information to run reports off. These all work fine, my problem is my 'Search Form' - below

I have created a query that finds information from 'all information' using

Like "*" & [Forms]![SearchAll F]![txtDateRasied] & "*"

This is working on all text boxes, It only half works on the combo box's when I use

Like "*" & [Forms]![SearchAll F]![combofailureanalysis] & "*"

If a selection is made in the combo box the query brings the correct results, however, if all the fields are left blank it should bring up every record, but it doesn't do this. I am certain it is the combo box's that are causing this anomoly as when I remove the combo box criteria it works perfectly again.

View 11 Replies View Related

Forms :: Apply Filter To Return Records That Matches The Text Exactly

Aug 2, 2015

I am using an apply filter in a form to find records in a split form.

The macro which I am using is:

[SLIDE] Like "*" & [Forms]![SALES]![ENTERREFDATA] & "*"

However I the filter to return only records which match the text exactly.

View 2 Replies View Related

Queries :: Filter Using Date Range But Show All Records If Text Box Null

Jan 13, 2014

I m trying to make form which filters my records and generates a report..here's where I am

Code:
Like "*" Or Between [Forms]![Form1]![Text6] And [Forms]![Form1]![Text8] & "*"

but this doesn't work I would like to show all records if textbox 6 is null and textbox8 is null this part of code works perfect but below but I'm struggling to get the between in with the code

Code:
Like "*" & [Forms]![Form1]![Text6] & "*"

the code is in report record source

View 9 Replies View Related

Forms :: Search Records And Add Multiple Date / Timestamps

Apr 11, 2015

Below are what I have created.

Table 1 with 3 fields:
- UniqueID
- field1
- field2

Form 1
- text box to enter UniqueID
- button to save the new UniqueID and date/time stamp
I set the Default Value for UniqueID in Table 1 as Now()

Each time I enter a new UniqueID and click the button, a new record will be created in Table 1 with the entered UniqueID and current date/time in field1

This form seems to work well.

Form 2
- text box to enter UniqueID
- button to update

How I can use the UniqueID entered into the textbox of Form 2 and update the corresponding field3 with a new current date/time stamp?

View 1 Replies View Related

Modules & VBA :: Errors With Custom Filter - Data Full Of Records With Multiple Columns

Sep 11, 2013

Custom filter I'm trying to set up. I have a data full of records with multiple columns and a form linked up to it. The form has a search box that works as well as navigation buttons.

Currently I am trying to get a filter to work. The filter will use up to five combo foxes to narrow down the fields, then return the filtered records after clicking a button. The button itself is where my code is. My issue now though is I cannot get the filter to work if I try to use more than one combo box.

The code I have at the moment is as follows:
'------------------------------------------------------------
' cmdApplyFilter_Click
'
'------------------------------------------------------------
Private Sub cmdApplyFilter_Click()

Me.Filter = "[Asset Group] = " & Me.cmbFilter1 & "" And "[Location] = " & Me.cmbFilter4 & ""
Me.FilterOn = True

End SubThe current error I am getting is Error 13: Type mismatch. I also know that the And might be the cause but I cannot change it to AND: it keeps changing back.

View 9 Replies View Related

General :: Search Records Based On Multiple Check Boxes?

Jan 3, 2013

I have a table of data regarding companies (contact info, etc). The company I work for provides these companies with up to 10 different products. On my input form, I have created 10 check boxes (and thus, 10 columns of Yes/No data in the corresponding table).

Each company has at least 1 product checked off, and up to all 10.

I would like to create a combo box that lists all 10 products, and upon selecting one, a list box then populates with the names of the companies (primary key) that use that specific product.

So, for example,

Company A buys CATS and DOGS from us
Company B buys DOGS
Company C buys CATS and ELEPHANTS
Combo box options: CATS, DOGS, ELEPHANTS

If I choose DOGS, then a list box gets populated with:

Company A
Company B

and when I click either of these, the record should be brought up for the respective company.

All of this should take place in the header of the form, while the form itself can be updated based on search selections.

View 3 Replies View Related

Forms :: Multiple Field Screen Search - MSG Box Not Coming Up If No Records Found

Aug 18, 2015

I have created a multiple field search screen. The script is attached.

I would like to add a MSG Box "No records found" when the user types in information in any field where no records are available.

Just cannot get this working.

View 10 Replies View Related

Forms :: Search Multiple Fields In Table And Open Matching Records For Editing

Dec 10, 2014

Create form to search multiple fields in table

Return records that match search

Open the record that you want in Form View for editing

View 1 Replies View Related

Queries :: Multiple Combo Boxes And Text Boxes On A Search Form

Mar 24, 2014

I'm trying to build an database for aircraft operators. I've got the basic tables structure and relationships but I'm stuck on building an search form to filter records by user input.I've got following controls on my form (unbound):

1. AircraftType (combo box) from tblAircrafts
2. CompanyName (combo box) from tblListOfAircraftsOperators
3. TeailNumber (text box) from tblAircraftOperators
4. AirportNameSearch (combo box) from tblAirports
5. PassengersNumber (text box) from tblAircraftOperators
6. ManufactureYear (text box) from tblAircraftOperators
7. SourceSearch (combo box) from tblInfoSource
8. CountrySearch (combo box) from tblCountry
9. CategorySearch (combo box) from tblAircraftCategory
10. EamilToOperator (text box) from tblAircraftOperators
11. InteriorPhoto (Bound object frame) from tblAircraftOperators
12. ExteriorPhot (bound object frame) from AircraftOperators

I need to enable users to search for aircrafts based on those criteria. As I mentioned I'm new to Access and I don't have any advanced coding skills. I have a query build to perform the search and this is the code I've managed to write so far:

SELECT AircraftOperators.RegistrationNumber, AircraftOperators.PassengersNumber, AircraftOperators.ManufactureYear, AircraftOperators.EmailToOperator, AircraftOperators.ExteriorPhoto, AircraftOperators.InteriorPhoto, tblListOfAircraftOperators.OpratorName, tblAircrafts.AircraftType
FROM tblAircrafts INNER JOIN (tblAirports INNER JOIN (AircraftOperators INNER JOIN tblListOfAircraftOperators ON AircraftOperators.CompanyName =

[code]....

View 2 Replies View Related

Moving And SORTING Multiple Column Data Into One Column

Feb 25, 2008

Hi. I have a question I'm hoping someone can help me with. I would like to take data from multiple columns and put the data into one column. Additionally, I do not want to exclude any data (union all) and I would like to group the resulting union by another field. For example:

Original data layout:

Column Headings: Sample Event, Depth 1, Depth 2, Depth 3,
1st Row Data: 1, 6, 9, 12, 9
2nd Row Data: 2, 7, 9, 8, 3

Desired data layout:

Column Headings: Sample Event, Depths
1, 6
1, 9
1,12
1, 9
2, 7
2, 9
2, 8
2, 3

So far I'm using the following SQL. What do I need to add or change to get my desired result of grouping the unioned depths by the 'sample event' field?

I appreciate any help anyone may have to offer. Thank you.

SELECT Depth1 AS Depths
FROM Depth_Velocity_Substrate_Correct
Union all
SELECT Depth2
FROM Depth_Velocity_Substrate_Correct
Union all
SELECT Depth3
FROM Depth_Velocity_Substrate_Correct
Union all
SELECT Depth4
FROM Depth_Velocity_Substrate_Correct
Union all
SELECT Depth5
FROM Depth_Velocity_Substrate_Correct

View 5 Replies View Related

Display Text From One Memo Field And Multiple Records In A Report

Apr 10, 2008

I have an Access database where we grade reports. At the end of the grading form I have a memo field [Comments] for the grader to enter comments about why the report received the grade [Grade]. Each report is graded by multiple people. So for example, ReportNum 08-001 may be graded by 5 people and therefore will have 5 records in the database. I need a report to show all the comments for all the reports where the grade is <80. I know how to do this except for how to display all the comments from the memo field in the 5 records on one report for that ReportNum.

I hope I am making sense. I appreciate any help.

Jim

View 2 Replies View Related

Modules & VBA :: Creating Multiple Records Using Button - Text Box To Say How Many To Create

Jul 22, 2015

I am looking for a way to enable a user to fill in a number of fields on a form press a button(CreateButton) and duplicate the records how ever many times is stated in a text box(TxtQty). I also need this to increase the serial number by the amount of times stated in the text box.

There will need to be another text box (TxtNextSerialNumber) stating the next first available serial number

For Example:
TxtNextSerialNumber = AD-Oracle-00010
TxtQty = 5
Press CreateButton to create 5 records
TxtNextSerialNumber = AD-Oracle-00014

5 new records created with the below fields duplicated and the above happening.

I will then need a message box informing the user of the serial numbers created:

'You have created serial numbers AD-Oracle-00010 to AD-Oracle-00014'

Table Name: ADOracle
Form Name: ADOracleTestData

Fields Names:
CustomerName: Duplicate
PartNumber: Duplicate
OrderNumber: Duplicate
OrderDate: Duplicate
HoseKit: Duplicate
Returns: Duplicate
Comments: Duplicate
SerialNumber: + the amount shown in TxtQty starting on next serial number available.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved