Queries :: How To Find If Memo Contains A Specific Word
Mar 18, 2015
I need to find whether a memo field contains a specific word. I know how to find whether it contains a specific string.
Let's say I am looking for the word "run." I would not want a positive result when searching "I don't like gerunds," but I WOULD want to find the records with "I know how to 'run' a search."
So I am looking for WORDS, not matching strings.
I am perfectly willing to use a user-defined function, to put involved processing into the query, such as
Code:
...
WHERE FieldHasWord("run", memoField) = True
AND ...
I asking to see if it is possible to run a query on a memo field. For an example I want to pull all records where the memo field contains the word "Test".
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.
So there's a memo field in my database that has a copies of an entire email message. I was wondering if there was a way for me to search for, extract, and place specific information from the text in the email into a new field automatically for each of them.
I have a column named "stuffno" that i should enter the id of my stuff.I should enter "no 111".I did this because some stuff dont have id's so if i make a calculated field. "No " & [stuffno]I will get in the field of stuffno1 "no " for the stuff without id..Ok now i want to make another column that contains just the id of the stuff..Is there anyway to take just the id numbet the contents of "stuffno" field without "no " so the "stuffno1" will be empty or it will contain just the id.
I am working on a school project called employee skills. I have a table of skills, each has a check box, which if checked is true. I am trying to write a query that will find who has a specific skill.
I have a table that is linked into access 2003. This table is updated by personnel in another location and I have to run a weekly report on engines that are below a certain performance level.
The column heading is MGT Margin and I have to list all of the engines that are below 20 degrees.
Can I run a query that looks at this table and produces a report of all the engines that are below 20 degrees?
I currently have to cut and paste each engine from the updated spread sheet every week onto a separate spread sheet and import that into access. If a query can be used to do what I am after I can use similar principles in other reports I have to run.
how to read a specific line in a CSV file (using VBA), to see if the phrase "There are no records available." is present.
If it is present, then I'm going to do a debug.print stating that there are no records to load - and then the script will move on to the next file. If the phrase isn't present, then I'm going to upload the file to Access, parse the information, and then upload it to a CRM. (I already have the latter portion of the code up and running....I just need to account for the first part, where I can determine if the file has data or not).
The structure of the file never changes. The first row is composed of eight column headers (Post Date, Card Number, Card Type, Auth Date, Batch Date, Reference Number, Reason, Amount) and (if) the phrase "There are no records available." is present, it will show up on the second row, in the first column (under Post Date).
I am building a database application, in which the users desire to use Word to Edit and change the RTF memo field of a particular table. I know how dump access rtf information to Word. Is there a way to pull it all back from the word application? Maybe some way to create a submit/close button that sends the data back?
Hi there, I am currently working on our intranet at work and am creating an Access database so we can record all of the box files at our storage facility.
One of the fields is called 'contents', which is a Memo field, where the person who is inputting the data will describe what is in the box. They will then use the autonumber generated by the database to label that box.
That way we can locate the boxes much easier than at the moment.
I have created a results page with a search box at the top using ASP in Dreamweaver CS3 and it works fine if I replicate EXACTLY what is written in the contents memo field.
ie: if the contents field for record number one reads
'Archived box of insurance documents dating back to 1999'
I would need to type in that exact phrase to pull that info back.
What I want it to do is if I was to put in '1999' or 'insurance' then it would return the same result (and others with those specific words).
What do I need to do in my code?
Here is the current code
Code:<%Dim rs__MMColParamrs__MMColParam = "1"If (Request.QueryString("content") <> "") Then rs__MMColParam = Request.QueryString("content") End If%><%Dim rsDim rs_cmdDim rs_numRowsSet rs_cmd = Server.CreateObject ("ADODB.Command")rs_cmd.ActiveConnection = MM_intranet_STRINGrs_cmd.CommandText = "SELECT * FROM archive WHERE content = ?" rs_cmd.Prepared = truers_cmd.Parameters.Append rs_cmd.CreateParameter("param1", 200, 1, 255, rs__MMColParam) ' adVarCharSet rs = rs_cmd.Executers_numRows = 0%><%Dim Repeat1__numRowsRepeat1__numRows = -1Dim Repeat1__indexRepeat1__index = 0rs_numRows = rs_numRows + Repeat1__numRows%><%Dim MM_paramName %><%' *** Go To Record and Move To Record: create strings for maintaining URL and Form parametersDim MM_keepNoneDim MM_keepURLDim MM_keepFormDim MM_keepBothDim MM_removeListDim MM_itemDim MM_nextItem' create the list of parameters which should not be maintainedMM_removeList = "&index="If (MM_paramName <> "") Then MM_removeList = MM_removeList & "&" & MM_paramName & "="End IfMM_keepURL=""MM_keepForm=""MM_keepBoth=""MM_keepNone=""' add the URL parameters to the MM_keepURL stringFor Each MM_item In Request.QueryString MM_nextItem = "&" & MM_item & "=" If (InStr(1,MM_removeList,MM_nextItem,1) = 0) Then MM_keepURL = MM_keepURL & MM_nextItem & Server.URLencode(Request.QueryString(MM_item)) End IfNext' add the Form variables to the MM_keepForm stringFor Each MM_item In Request.Form MM_nextItem = "&" & MM_item & "=" If (InStr(1,MM_removeList,MM_nextItem,1) = 0) Then MM_keepForm = MM_keepForm & MM_nextItem & Server.URLencode(Request.Form(MM_item)) End IfNext' create the Form + URL string and remove the intial '&' from each of the stringsMM_keepBoth = MM_keepURL & MM_keepFormIf (MM_keepBoth <> "") Then MM_keepBoth = Right(MM_keepBoth, Len(MM_keepBoth) - 1)End IfIf (MM_keepURL <> "") Then MM_keepURL = Right(MM_keepURL, Len(MM_keepURL) - 1)End IfIf (MM_keepForm <> "") Then MM_keepForm = Right(MM_keepForm, Len(MM_keepForm) - 1)End If' a utility function used for adding additional parameters to these stringsFunction MM_joinChar(firstItem) If (firstItem <> "") Then MM_joinChar = "&" Else MM_joinChar = "" End IfEnd Function%>
Memo field is called [Notes] and data is like this...
5/05/14 - Perry was on another call. LM 2/05/14 - Perry only at centre in the mornings, need to speak to him before sending samples. 13/06/13 - Perry in a meeting. lm 30/05/13 - See Little Hampton Early Learning - s/w Perry, has already received sample and info 29/05/13 - s/w Aspi, said to cb tomorrow and speak to Perry
I want to find each date in the Notes field so I can split the memo field data into another table where the memo field will become multiple records that hold date, text and customer/prospect ID fields. The customer table was easy because there was a <Div> tag before each date. However in the Prospects table there are no tags so how to change my vba code to search for each date before I split off the data.
Here is the part of the VBA code I used to find the <Div> tag in the customer notes field. How to find each date in the memo field? The date is in d/mm/yy format?
If Not rst.EOF Then Do StrSplit = Split(rst![Notes], "<Div>") For x = 1 To UBound(StrSplit)
A customer's name is SZEE. Seek him through the SName textbox with Find, and Access can't find him. (Same in the table.) Seek him with a wildcard Sz* and there he is.
I've tried it on another machine - also with Access2k - and it's the same.
Is it an Access quirk? Is there an answer? (The client asks why. Be good to be able to say.) Cheers.
I had the need every so often to quickly find all instances of any (particular) word within the database, without going to the bother of thinking about where that word/subject might be in the data base, I designed a report which does the job nicely. It uses a search form/control, searches and finds all instances of the word (or words) I have asked it to find, then does a report showing all instances of whatever RTF memo fields that contain the word(s).It has to be RTF memo field because (if I wish) it goes straight into the book text I'm writing, and also it searches the book's RTF memo field. . The trouble is, at present, I then have to manually "eye scan" the resulting report to find the occurrences of the "key searched for" word, nestling/hidden somewhere within sometimes quite large chunks of text. I would love to have the report show the found "key word(s)" highlighted in (say) light blue or whatever colour, so that I can quickly & easily lock on to it visually.
I enter the word I want Access to find, into a form called "formfindword" in a text box called Text0. I click on a command box on this form, to open up the report called "Querywhatsandhowschooseword" based on a query of the same name. The query's "what" field (from the main database) has the criteria
= Like "*" and [forms]![Formfindword]![Text0] & "*"
The query's "how" field criteria (in the "or" line of the query design) is also
= Like "*" & [forms]![Formfindword]![Text0] & "*"
I have tried to use the Conditional Format facility in Report Layout view, without any luck.I think therefore that I will need some VBA code (which is totally foreign to me, unfortunately) to make this simple concept a reality.
I need to print a report on pre-printed paper. The name fields are in blocks, which means I need to print the first character of the name in the first block, the second in the second block, etc. Any ideas??
I am trying to Dcount in a report agianst a query but I need to narrow down to a specific name within the [Catgory] for example [Category] ='Allowable' How do I fit that into the statement?
I've almost completed the DB I'm working on, but still have a few loose ends to tie up that I can't seem to figure out. I've spent many hours already on this forum searching for solutions, and have tried a few different things, but I still can't work out these bugs.
First, I need my form to open with the latest records filtered first, so that the most recently added records will be easiest to find and edit. I'm not sure how exactly to do this. I've tried a run query when the form is opened, but I still need all the records displayed or accessible. The form is called frmDenial, and I would like it to be looking in the DateLogged field of the form. Any suggestions?
Second, I have command buttons that will export data in the fields to MSWord Templates via bookmarks. The button runs well by opening the document, inserting the data, and printing. I have also tried several different code syntax to close word once it is done, but it is still staying open after printing. Here is a sample of the code: ________________________________________ Private Sub Print_Letter_Click() Dim objWord As Word.Application 'Start Microsoft Word 2000. Set objWord = CreateObject("Word.Application") With objWord 'Make the application visible. .Visible = False 'Open the document. .Documents.Open ("G:PharmacyPrior Auth Docs and DataRevised Pharmacy Denial ProcessesKAN Not Nec or Benefit2.dot") 'Move to each bookmark and insert text from the form. .ActiveDocument.Bookmarks("bmkFirstName").Select .Selection.Text = (CStr(Forms!frmDenial!MBRFirst)) .ActiveDocument.Bookmarks("bmkLastName").Select .Selection.Text = (CStr(Forms!frmDenial!MBRLast)) .ActiveDocument.Bookmarks("bmkHRN").Select .Selection.Text = (CStr(Forms!frmDenial!MemberNumber)) .ActiveDocument.Bookmarks("bmkAddress1").Select .Selection.Text = (CStr(Forms!frmDenial!MBRAddress1)) End With Print_Letter_Click_Err: 'If a field on the form is empty, remove the bookmark text, and 'continue. If Err.Number = 94 Then objWord.Selection.Text = "" Resume Next End If objWord.Application.Options.PrintBackground = False objWord.Application.ActiveDocument.PrintOut objWord.Application.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges 'Quit Microsoft Word and release the object variable. objWord.Quit Set objWord = Nothing Exit Sub End Sub _______________________________________
Finally, I have two cascading combo boxes set up that will auto pop related fields based on the selection made. But, if an item is not in the list, I would like the user to add it to the linked table to appear in the list. I also have this working well, with a pop up asking the user if they want to make the addition, type in the new item, and add to the list without requiring the user to refresh or exit then re-enter the form. But it will only add the item name, and not the item description (another field in the form and another column in the table). How can I modify the code to prompt the user to enter these other details? I can link it to a pop up sub form to enter the data, but if possible, would rather the boxes pop up to have the user type in the data. Here is the code I have so far in the NotInList Event... __________________________________________ Private Sub DrugName_NotInList(NewData As String, Response As Integer) Dim DB As DAO.Database Dim rs As DAO.Recordset Dim strMsg As String strMsg = "'" & NewData & "' is not an available Drug" & vbCrLf & vbCrLf strMsg = strMsg & "Do you want to add the new Drug to the current Database?" strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to add or No to re-type it." If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new model?") = vbNo Then Response = acDataErrContinue Else Set DB = CurrentDb Set rs = DB.OpenRecordset("tblDrug", dbOpenDynaset) On Error Resume Next rs.AddNew rs!Drug = NewData rs.Update If Err Then MsgBox "An error occurred. Please try again." Response = acDataErrContinue Else Response = acDataErrAdded End If rs.Close Set rs = Nothing Set DB = Nothing End If End Sub ______________________________________________ The two other colums in the tblDrug that I need the user to be prompted to fill are Denial Reason (column 3) and Alternative (column 4). What would be the best way to accomplish this?
I would really appreciate any help or suggestions with any of these problems. Thank you so much! :o
I was thinking today if it is possible to fill Access reports based on a specific Word template? I don't want to populate a Word file with Access data as users of my app might not all have Word. Or is there another solution using a WYSIWYG editor where users can format everything according to their needs? That would be fantastic.
We need to move rich text from an Access memo field to a Word text box. So far the best Ive been able to come up with is in the code below. In this code pprs!What is a record set field of a table memo field that is bound to a text box enabled for rich text. The rich text seems to be stored as html as so I can get word to convert it by enclosing it in html tags.
Dim What As Word.Shape Set What = doc.Shapes.AddTextbox(msoTextOrientationHorizontal , doc.PageSetup.LeftMargin, 225, 534, 0) Dim sPath As String sPath = "G:Temp.html" Open sPath For Output As 1 Print #1, "<HTML>" & pprs!What & " </HTML>" Close #1 What.TextFrame.TextRange.InsertFile (sPath)
I am wondering if there is a quick way to find a database object (table, query, form, report) other than having to scroll through them in the database window.
E.g. I have many many queries and when I need to find one to make changes to it, it often takes me a while to find it when scrolling thru the queries in the database window.
Has anyone ever programmed anything whereby you can type in the object type and name and then it will be highlighted in the database window?
Note: Dates are in "dd/mm/yyyy" format. Also, not all data is in order in the table. It’s not how I would have designed it but that’s what I need to work with!
I would like to run a query that groups by custID & itemcode and also the most recent date for any price change. I would then like the query to display the relating price for the record it pulls out. (EG for custID 'AAA' and item 21, it would display the price of £5 as that is the value for the most recent price as of 01/06/2005) I then want to link the custID & itemcode to another table that has sales in, to check that we are using the most recent price.
Is there a way I can do this via a query or do I need to put some code together?
Having problems with the following function to goto a specific record and return a value from a field in that record.
Code:'Public Function intFieldZConv(strICAO As String) As IntegerPrivate Function intFieldZConv() As Integer 'TEST PURPOSE ONLYDim strICAO As String 'TEST PURPOSE ONLYstrICAO = "KTCM" 'TEST PURPOSE ONLYDim cnCurrent As ADODB.ConnectionDim rsFieldInfo As ADODB.RecordsetSet cnCurrent = CurrentProject.ConnectionSet rsFieldInfo = New ADODB.RecordsetrsFieldInfo.Open "tblFieldInfo", cnCurrent, , , adCmdTable'rsFieldInfo.Index = "FieldICAO"'rsFieldInfo.Seek "=", strICAOintFieldZConv = rsFieldInfo!FieldZConvSTMsgBox intFieldZConv 'TEST PURPOSE ONLYrsFieldInfo.ClosecnCurrent.CloseSet rsFieldInfo = NothingSet cnCurrent = NothingEnd Function
strICAO is received from another function which is to be used to determine the specific record in the table "tblFieldInfo". Once that record is referenced, the value from the field "FieldZConVST" is returned.
My problem area is highlighed (index and seek) which I receive an error stating "method or data member not found". Any help would be appreciated on how to finding a specific record.
I have many queries for separate types of income that when criteria is entered on the switchboard, a report is created for either a specific month or range of dates. This works fine. Now I need to create one report that shows all 7 types of income in one spot, but once again based on a specific month or range of dates. I have a query that produces the entire income for all dates, but can't get it narrowed down to the specific criteria.
Hi well as the title says i have a database with a LOT of tables, and i need to find the tables that contain a certain heading, eg reference 6, is there an easy way of doing this?
Each Product can be in the table mulitiple times depending on how many Build ID's it has. So if Product 123456 has Build ID's G004, E818, N005, F813, D024, C879 it will show up one time each for each Build ID. What I want to query is unique Products that are for Build ID's D024 & C879 only and not for G004, E818, N005, & F813.
How can I accomplish this with a query. I am sure I am making this harder than it is but I sure need help.
