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.
I'm creating a search form to filter out data based on certain inputs. My VBA code looks like:
Code: Private Sub Command18_Click() On Error GoTo errorcatch Me.Filter = "([Experiments.Log] Like ""*" & Me.Text21 & "*"") AND ([Expdate] Like ""*" & Me.Text22 & "*"") AND ([BaseSolution] Like ""*" & Me.Text24 & "*"") AND([AddCom] Like ""*" & Me.Text25 & "*"") AND ([Test] Like ""*" & Me.Text26 & "*"") AND ([Plan] Like ""*" & Me.Text23 & "*"")"
Me.FilterOn = True Exit Sub errorcatch: MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description End Sub
However, the output does not include records where other fields are blank. I have read that I may need to use Is Null but am not sure how to.
I currently have a database with a few search forms. I recently attempted to add a box on one of my search forms to search 3 cells of a record for a key word. What happens is when I go to search, say for P0442, it does not bring up all of the records that contain that keyword in the 3 cells I have outlined. If I step down the code in the OR boxes of the query, it seems to work better but then for some reason my other search criteria , (Tracking number etc) does not work. I will upload the database for reference. I am currently working on the Search(View Only) and that is where you will find the query to work on.
I recently made a new version of our database complete with forms, querys etc.. using Access 2010. It looked good and nice, but we encountered an unexpected problem. In all forms that contains memo-type fields quick filter is not working (there is no lists, checkboxes etc..). My users really like quick filters and are understandably irritated. I would understand if this disappearance would affect only memo-type fields, but it affects ALL the fields.
I also know that there are problems with memos in Access 2010. I have tried to make new forms, but every time I add memo fields quick filter stops functioning. I have also tried to change properties, options and what not, but to no use. In older versions of our forms this feature works just fine, but they were made in older version of Access (don't know which since oldest ones are from year 2003). Television
creating a VBA that will allow me to search for a record in Table A, and once the record is found, allow me the option to add that record to Table B. Is this possible to do if Table A and Table B are not formatted the same (i.e. one has more fields than the other)? In summary, creating a VBA that will allow me to search in one table and once the correct record is found, allow me the ability to add it to another table.
I import a CSV field which has not preserved the CR/LF when it was exported from BCM Remedy. There is no setting for that. The memo field prints on my report like this:
A custom solution would be developed, that once implemented, could become the standard product in similar situation. Normally would assign to Network Engineering, but will work with Ray and the IPT Team to cost out the solution and get approval to proceed. 2012/05/24 10:44:28 AM PCOLLINS Sent to Ray Massie for review to determine if a solution needs to be proposed, or if they can wait for the National IPT solution to be ready in 2013.
I want to add VBA code that inserts a CR/LF in the memo field before all but the first occurance of a string that looks like a date, the first occurance doesn't need it. I will do it right after I import the CSV file into the table, so it happens only once, and it always prints and displays the CR/LF.
The memo field is called "NBS Update" and the table is called "CCRR Remedy Data"Here is what I have, but don't actually know what to put in to find the date and add a CR/LF:
Code: Dim db As DAO.Database
Code: Dim db As DAO.Recordset Dim srtSQL As String
The following is code to open a form and records with a specific date as input by the user:
Private Sub cmdDisplayCovers_Click() On Error GoTo Err_cmdDisplayCovers_Click Dim stDocName As String Dim stLinkCriteria As String stDocName = "frmInventory" stLinkCriteria = "[tblCovers].[Date] = [Forms]![frmCoversByDate]![txtDate]" DoCmd.OpenForm stDocName, , , stLinkCriteria etc. etc.
generate a message to user if no records match the input and then return to the input form. Currently, the form opens even with no records.
I want to set up a 'keyword search' facility on my dbase. I would like the user to enter a keyword in a dialog box and for this to then open my main FRMContractInfo form with a recordset that shows all the records where this word appears in any or each of four fields (Summary, Methodology, Lessons, Successes). I am a bit confused about how I should approach this, can someone offer me some advice?
Do I:
a) set up a Query that references the text box on the dialog box and code it as the open form's recordset b) write the SQL for the form in the code?
Or are neither of these approaches correct? I've tried both and neither seem to work - but I think I've got my syntax all wrong because of trying to use Like and the wildcard (*) character...
I hope someone can help me. I am using Access 2003 to create a database to keep a track of projects in my craft magazines.
I have a table that gives the magazine name, issue no, project name, page no and keywords. When I enter my data I will use various keywords to describe the project. For example, for a football card for a teenage boy I would use keywords such as football, boy and teenager.
What I need to be able to do is have a query based on this table that if I search for "football" it will pull up all records where I have used football as one of the keywords.
In my current query I can do this providing "football" is the only keyword I have used, but if I have used more than 1 keyword in my table then my query will not show any results.
Is there any way I can do this?
Hope you can understand what I am asking, and if someone can help, I would appreciate simple instructions as I am not very experienced with access
HI, Im trying to create a keyword search based on the data in several fields. The below command works well for searching one field, but I want to search several at once. Any help would be great. Thanks!
What you need is a parameter query. In the criteria space in the desired field in your query, put the following: Like "*" & [Search by word:] & "*"
When you run the query, a message box will pop up that says "Search by word:", and then you could type in "mountain" or "goat" or even just type in one or two letters. To make it more/less specific, play around with those wildcards.
I would like to have a search box in which someone can type anything and it will search through a few tables to see if this word appears, and then return information from that record so I know who it belongs to. I have a query written that has all of the fields from all of the tables that I want the search to cover, but now I am having trouble connecting a button to the query so that it runs the query and puts the word as the "Like" criteria. I can make the button run the query, but I didn't know if I have to put Like * &[TextBoxName] & * in the criteria of each of the possible fields? I would appreciate your help with this. Thanks:)
Hello all, once again I would very much appreciate anyones help on what may be quite a simple issue,, my problem (or one of!!) is as follows: I have a table that contains approx 3k records I wan to run a query that allows me to select a certain number of fields with a group. From this I mean, I have all records grouped by discipline, firstly its easy for me to determine the discipline I require (I do this in the design view by adding the discipline number in the criteria row.) next I want to find a certain number of the records that are within my chosen discipline,, the records I wan to see all contain the word ‘alignment’. I tried entering ‘alignment’ on the criteria row but that didn’t get me anything,, the field that contains the word alignment also has many other words listed… Please help Many thanks Jon
As shown in the attached database, I have a multiple criteria query with a front end (see frmIssue). I am trying to add a keyword search on the field 'Issue' by having an unbound textbox in frmIssue and linking it with a 'Issue' field in the query 'MyQuery'.
An example search would be looking for an issue with the keyword 'misfiring' in project 'A'.
However, having tried the Like expression on the field 'Issue' a few times, I'm still having trouble making it work. Any suggestions would be greatly appreciated!
I have search the web and came across something pretty similar to what am looking for (URL...), unfortunately this database sample does not work with my Access 03, so I cant really play with it.
What I want to do is have 3-4 Paths save on my form to folders on our office server, this is were we keep client files. I like to have the ability to search for individual folders by either client ID or name or even partial name if possible too, knowing that it may display more than one folder for the employee to pick and open.
I've got an issue pertaining to inner joins within an update query. This query will not run now (giving the expected "must use an updateable query" error) due to two fields involved in one of the inner joins are now both memo fields.
Is there any way (and I assume there must be in vba) to recreate this query so it will run with inner joins involving memo fields?If not, is there a way in vba to do a find and replace within a table? For example, replace the "A" in "A-001" with "B"?
I'm trying to hash two scripts I've found into 1 functioning filter, however I'm still relatively new to vba and can't figure out how to get this working.
I'm trying to use Allen Browne's Search Criteria:
with another snippete of code I found here:
Code: 'Purpose: This module illustrates how to create a search form, _ where the user can enter as many or few criteria as they wish, _ and results are shown one per line.
[Code]....
It's the date part I'm having trouble with, the rest of the search criteria work fine without the date, but I can't get it working when I try to modify and merge the date sections of each code.
Also I'm using a listbox for the "Yesterday";"Last 4 days";"Last 9 days" and not a combo box.
I have a form in access where i need to select a record between two different dates. For example i have a "valid_from" and "valid_to" field. I have an unbound text box with short date format and calendar control inserted for users to select a date. This is named "drpdate".
I have a bunch of other filters also in the same form. Now my issue is that i have not been able to figure out how to put in a SQL statement which would give me the data which is between the "Valid_from" and "valid_to" fields based on the date selected in the unbound text box.
If the selected date does not meet the criteria, then it needs to be give the results from another table (which i have already done).
I am trying to get a multiple keyword search query working.... the single search is working however when i use the Multiple Keyword search the data sheet does pop up but no matter what i put in i just get a blank table and under ID it says "NEW"
The Keywords Column has a bunch of keywords separated by commas on each row. Sometimes there will be the same keywords on different rows under the keyword column... and i want it to return any rows that have 1 or more of the keywords in it or if it is null just to return them all....... this is the statement I am trying...
SELECT Sea.ID, Sea.Category, Sea.Title, Sea.Author, Sea.Organisation, Sea.Date, Sea.Keywords, Sea.Reference FROM Sea WHERE (((InStr([Keywords separated by commas,Blank=All],[Keywords]))>0 Or (InStr([Keywords separated by commas,Blank=All],[Keywords])) Is Null));
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.
I'm relatively new to Access 2010. I have an excel spreadsheet that I've converted into an access table for the purposes of creating a user friendly form for the end user to search for relevant criteria to retrieve a list of records that fit the requests. There are two main search functions: unbound keyword search (90+ keywords), and either a series of checkboxes or one list box that lists specific countries/regions matched in a column (columns, as I will get to shortly). The region selection is bound by a selection of 15 countries.
The key is each record can correspond to more than one country. This, one record can have "Belgium, Germany, brazil" and the next record can just have "brazil". Thus if someone selects brazil, the query must retrieve the Belgium Germany brazil record in addition to the record that covers just brazil. I've tried to do this with a concatenated column that list all countries and a text box search, I've tried to do this with individual checkbox columns for each country with checkboxes in the search form...and I can't get it to work. I don't fully understand the criteria to set for checkboxes or list boxes. What would be the best way to build this form, an how?
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!
