Search For A Word Anywhere In The Table With Certain Criteria?
Apr 7, 2015
I have a table of information in Access and I was wondering what would be the best way to get the info I need out of it.
for example can I search for a specific word that could be anywhere in the table in any of the cells.
The word is 'pump' and I would also want to know if it is an inception pump or not and then I would like to see a list of clauses associated with the pump (the clauses would be in a specific column under a heading)
My end goal is to populate a pre-existing table in an MS Word document with records from a query. The easiest way I've found (through scouring the internet) is to start with the code below (ran during OnClick() even in Access) to get the table the same size as the recordset:
Code: Dim wDoc As Word.Document Dim wTable As Word.Table Dim wCell As Word.Cell Set wDoc = appWord.Documents.Add(strDocLoc) wDoc.Visible = True
[Code] ....
The code will shrink the table down just fine if the table has more rows than the recordset +1 (for header column). My hangup with this is the last line ("Selection.InsertRowsBelow 5") isn't executing; rows are not being added to the table. I get no errors -- it just does nothing. I set it as "Selection.InsertRowsBelow 5" arbitrarily just to see if it would even add rows, and sure enough it's not.
1. I created a form with some search-fields which are related to a query. Then I added a Subform in which I put some more Search criteria (So that I can easily hide and unhide those additional searchfields). It sounds strange but is necessary ;-). Now I related those searchfields in the subform to the same query. When I run that query a window pops up that I should put in a value in all those searchfields which are in the subform. But I told Access that it should display all rows, if there is no value in those searchfields. Just as I did it with the Searchcriteria in the Main form. Do I have to do something special, when I have a query which is related to two Forms?
2. I want a searchfield to search in three different columns. Usually the value will just be found in one of those columns. As the Table I search is very long and has many searchfields and multiple of those will relate to more than one column, is there an easy way to do it in VBA? As I did it by using the "or" field when designing a query, but this seems very slow and unstable.
I am creating a a text box where the user enters a text then clicks an option from the option that is used as the criteria for the search e.g. Last Name, Phone , address then a command button wil run a query.
Hi everyone. I have the following code attached to a text box.
Private Sub txtsearch_AfterUpdate() Me.lstsearch.RowSource = "Select [R&D ID#], [SKU#], [Project Name], [Construction level], [Manufacturer], [Hobbico Status], [R&D Work By], [Product Manager], [Desktopper]" & _ "From [Project Main]" & _ "Where [Project Name] like '*" & Me.txtsearch & "*'" & _ "OR [SKU#] like '*" & Me.txtsearch & "*'" & _ "OR [R&D Work By] like '*" & Me.txtsearch & "*'" & _ "OR [Product Manager] like '*" & Me.txtsearch & "*'" & _ "OR [Desktopper] like '*" & Me.txtsearch & "*'" & _ "OR [R&D ID#] like '*" & Me.txtsearch & "*'" Me.lstsearch.Requery End Sub
This code searched for matching text strings in a few different filed. It is working really well, but it would become much more helpful if instead of searching one single text string, I could enter two in the same box and it would perform the search based on matching both strings, that is, list all the records that have xxxx and xxxx in any of the fields checked.
Is there anyway to use Like in queries where the user could type in more than one word?
I have a text box (search title), on a form (Search).
On after update the text box, opens a query.
The criteria in the query for the Title field is:
Like "*" & [Forms]![Search]![search title] & "*"
this works great for one word or phrase.
I would like the user to be able to type in 2 words, and have the query find all the records where the first word is in the Title, and where the second word is in the Title. And so on with 3 words or more.
I was tasked to create an application where by the user enters keywords into an Access form, and when he clicks the button, it will run the keywords against the file names stored in the table and automatically open the Word document that is the best match.
I have created a table query called Directory, which contains FPath (Z:), FName (Document1.doc) and Directory (Z:Document1.doc).
Code: Private Sub Command2_Click() Dim wrdApp As Word.Application Dim wrdDoc As Word.Document Dim filepath As String Dim strSearch As String
If IsNull(Me.txtSearch) Or Me.txtSearch = "" Then MsgBox "Please type in your search keyword.", vbOKOnly, "Keyword Needed"
[Code] ....
This is the code that I am currently using to search and open the Word documents, however, this code only manages to open Microsoft Word program without loading any documents. Also, there are no error messages when I click the submit button.
I have 5 textboxes and a multiline textbox in a form in my Access Database. I am going to type several keywords in those textboxes and I am going to type an article in the multiline textbox.
What I need to do is I want to calculate the keyword density in the article. I can do that if i know how to search the whole text in the article and count the specific word. And I want a function to count all the words in the article too.
I was trying to filter a word using Not Like "word" in my query cirteria, some data is blank also, From this record i want to retrieve the result. But when i use Not Like Criteria, filter is working but blanks columns are also filtered.
I want to display all records (including blanks) except what i shown in the criteria.
I have a search query (query by form) which is picking up keywords from a form and displaying matching results.
I want to add a tick box to the form, and if this tick box is ticked, I want the search to only include results which have a certain field NOT blank.
ie.
frmSearch will have tick box named "Website"
If "Website" is ticked on the form and the QBF run, the query will only show those records which have information in the field "Website Address" in the table tblPublication.
If the "Website" tick box it NOT ticked, the query will show all records, regardless of having information in the field "website address" or not.
can i do this in the criteria of the query?
I don't really want to do it by having a seperate query which is run by a seperate "Search" button on the form - this would be possible by having a 2nd search button (titled "Search for results with website") run a different query which has the critera that the field "Website Address" is not null.
I dont really want to have a seperate button and query as it makes it a bit messy - would rather the one query look up if the tick box, and if "ticked" then display only those with content in "website address" field, and if not ticked, display all regardless of content of field "website address".
I have one table with 4 fields TYPE CODE REASON DESCRIPTION SHORT DESR When I try to do a query to search on CODE it returns nothing. I don't understand what I'm doing wrong. Would someone please look at this DB and help> Thanks
Wonder if you guys can help me with something. I have a table with about 1200 guests, what I want to do is to search the table base on different criteria (or combination of criteria), namely phone #, name, street name, and postal code. Not everyone has all this info, and their names aren't separted into proper lastname or firstnames (old data).
What I want to do is to be able to type in a person's first name, last name, or both (an maybe other info if the first search wasn't successful).
http://www.psynic.com/files/access.jpg
What should I do to implement this? I was thinking of running 4 different queries, and interesect them into the final query. What do you think?
I have the date on my table as 01/02/2006, there are others like this, i'm wondering how i can search for the whole month, something like **/02/2006. i have tried that way and didn;t work.
Cant seem to work this one out. I have quite a complex search form. The underlying query displays the results in a list box on the same form.
So far I have used the following expression for all the fields on my form (whether text or integer values): Like "*" & [Forms]![Frm_FrmSearch]![AssetName] & "*"
This appeared to work correctly. However, now my Asset Management System is storing a number of Equipment Type's. As one of the query criteria is Equipment Type ID it means that selecting PC (1) also displays the details for Printer (11), Scanner (12) etc......
I know why it does this (because these numbers start with a 1 and I am using a like expression). However I cannot seem to get it working.
The equipment type value is present in every record so I dont think I can use =FormValue or FormValue Is Null. I did try: =[Forms]![Frm_FrmSearch]![EquipmentType] Or Like "*" & [Forms]![Frm_FrmSearch]![EquipmentType] & "*"
but it seemed to skip the first parameter and still displayed printers etc. as before.
Sorry if this is an easy question, but I've been racking my brain trying to get this one.
I have a reference table of sales agents and assigned territories. Ex -
Agent Territory Tom Smith IN, MI, TN, AR Deb Jones IA, KS, NE, MO
Now I want to assign agents to a list of prospects based on their state id. However, I can't just join the state field from the prospects table to this reference table. How can I get this to work? Any help is greatly appreciated.
I have a feeling this may be easier than I expect however I am at a standstill.
I have a Query that is called from an unbound list box when data is typed into one or all three unbound txtBoxes "txtLastName" ,"txtFirstName", and "txtVIN" the query populates the listbox almost as it should..
The purpose is to identify duplicate entries based on three critera, last name, first name and VIN with the VIN bieng an execption meaning that if the VIN does not match I still want the matched first and last names to remain in view..
When I open the form where the list and text boxes are all records show in the listbox and as I begin to type the last name all records that do not match that critera are dropped, the same goes for the first name this works great. Once I get to the VIN however if there is no match I loose all three and the listbox is empty.
Is there a way to maintain matched names in the list view eventhough there is no match for the VIN?
Below is the code I am using in the Query Design, it is the same for all three fields Lastname, FirstName and VIN.
Like "*" & [Forms]![frm NewOrderVINVerify]![VinToFindFen] & "*"
I have a form with drop down boxes that list 3 related fields and I have a search button that will requery based on the the input from these boxes. These boxes are all prepopulated with the data and I want to be able to select something from Box1 and then based off Box1 change whats populated in Box2 and Box3. Any idea's???
I already have a query setup like this to requery a query I make: ( I have a button that initiates the requery based off what input is given) Box1:
Problem with this is that is does not requery correctly and it only filters on one of the criteria ( Field1) and spits out all records for the other two?
So I figured since I already populate the drop downs with the records why not just change the contents of the drop downs? If anyone can give me some insight it would be much appreciated?
I have a form on which I have about 6 combo-boxes and a set of 3 radio buttons.
I'm to the point that all the querys that fill these combo-boxes are in place.
What I want now is a search button that updates a datagrid under it when clicked. The query in this datagrid needs to be parameterised with the selected values in the comboboxes or radio buttons. It should be possible to only select one combobox before pressing update.
At this point I placed a subform in the form to bind this query to. ( the datagrid I need). Is a subform the correct object for this or are there other possibilities?
For some boxes only a line to the where clausule of the SQL statement needs to be added. For some other (one) and the radio buttons a join needs to be made with another table. So I will have to build my sql statement dynamicaly in some vba code, run it when the search button is clicked and refresh the datagrid.
Does ab has a sample database in which such a search form is being used?
I must have downloaded like 10 sample DB's now but nothing I can use...
I have been trying to produce a front end for a multi criteria search. I have used one of the sample databases from the site and amended the code as necessary, but obviously not correctly. I can't get it to show me the records based on my search criteria.
I would be grateful if somebody could have a look and let me know what I've done wrong (cut down DB attached). If I can crack this I want to do another multicriteria search for other parameters.
One other question - is it possible to take those filtered records and dump them into a report? For example, say I select one parameter and want tpo print all records associated with that parameter?
I am using the below code to open a form from a search form. This code works well because I could leave a search field blank, and the code would treat the blank search fields as a wild card search. Here is the problem; I want to be able to search a range of ages in addition to lastname and first name. I added two fields (“AgeStart”, “AgeEnd”) in the search form and added ([age]>= '" & Me.AgeStart & "*'and and [age]<= '" & Me.AgeEnd & "*'") to the end of the stLinkCriteria. This addition works well if there is an age range is entered into the search fields. If nothing is entered into the age range fields of the search form, access does not treat the empty age range fields as wild card like the other fields. I would like Access to treat the empty age range fields as a wild cards search. Is this possible, and if so, how would I go about doing this? Any help on this would be greatly appreciated.
Dim stDocName As String Dim stLinkCriteria As String
Has anyone ever come across an example of a form where you can carry out a multi criteria search which not only displays the results on a subform but when you select an item from that subform the details can be displayed in text boxes etc on the main form. I have tediously searched this forum and the web but all search examples only display on a subform only, is it even possible if so has anyone found any examples or how would I go about achieving this
May I know what is the easiest way to search for records using 2 fields wich are not primary keys? and then return a boolean value whether it is found or not...
These 2 fields are of integer type.
Recordset.Find can only find record with one field and not two.
Is there a way to search for queries that use specific criteria? Let's say I have 60 queries in total, but only 35 of them use the "Province" field as criteria. The criteria is set to retrieve all records that are in Province AB, SK, ON.
Suddenly we need to also include Province MB to all of these 35 queries. Is there a way to identify these 35 queries (all the queries use criteria in the "Province" field). These are the queries that would need to be modified to include "MB" as part of the criteria.
I hope my explanation is clear. Thanks upfront for any suggestions!
I want to ba able to search for only part of the field in a query. I want to run a reort from a query where the user only needs to enter part of the information from a product list ie the items listed may be as lisetd as make model in the same field. the user won't be able to get to the query to alter the criteria. i have tried: Like "*" & [Enter Product Name] & Like "*" Which I found on a previous post but this returns a syntax error, and highlights the second Like
