Hello,
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".
Is this possible?
Thanks!
Fen
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 ...
Can anyone help me in creating a field from a query which will pull the first word only from a specific field.
Thanks.
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?
View 2 Replies View RelatedHi 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%>
and for the search form:
Code:<form id="content_search" name="content_search" style="margin: 0px; padding: 0px;" action="archive_results2.asp"> <label> <input name="content" type="text" id="content" value="" /> </label> <label> <input name="button" type="submit" class="directory_form_button" id="button" value="Search Content" /> </label> </form>
Thanks for any help that could come my way!!
Cheers
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)
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.
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)
Greetings All -
Let me say first I am a novice Access user, just trying to teach myself a few things to make my job easier. So take it easy on me with any feedback.
This is my situation. I am using a web based help desk ticketing software that has an Access DB. I have created a couple of queries and linked them together that seem to be working and giving me the data that I want with one exception.
I am tring to query a memo field, which is the description of a ticket, and the output is only a small portion of what is actually entered in to the table. Is there a way either in my query or report builder to make a change that will output all of the data from the description field?
A couple of things that I've tried on my own are to change the data type for that field from memo to text, however that that puts limits on the amount of data that can be entered in and causes the help desk software to function incorrectly. I've also turned on Can Grow and Can Shrink for the text box for the output in my form.
Any help you can offer would be greatly appreciated.
Thanks.
Jason
Hello,
I have Custormers table.
Name : Text
Address : Text
BDate : Date
Notes : Memo
Sometimes, I need to query Memo field.
I have a CForm which has a field named Search.
I want to query all Notes fields in Customers table via CForm!Search field.
Should I make a query like this? I use IN but I doesn't work.
SELECT Customers.Notes, Customers.Name, Customers.BDate
FROM Customers
WHERE (((([Forms!CForm!Search]) In (Customers.Notes)));
Thank you for your help,
Osman
I am using a 'memo' field in a table because the field size needs to be more than the maximum for a 'text' field (255 characters).
Although the text appears in full in the table, when I run a query to select certain records from that table the text in the memo field is truncated to 255 characters.
Anyone know how to correct this?
Hi all,
I had table with following data
Table
f1 f2 f3
1 10 aa......
1 11 aaa...
2 10 bb...
2 11 bb.......
f3 is memo field
I had to retrieve data by grouping records based on f1 value
so i gave groupby in totals section to f3 field also.
I am getting the values correctly, but memo field is truncating.
Its only displaying first half arround 236 charecters only.
If i query directly without performing any group by
then i am getting entire data for the memo field.
please any one give the solution.
waiting for your help.
Thanks
I have a field [InternalComments] which is a memo field. Lots of data per customer
Can I make an update query to add data to the existing data without overriding the data currenty there?
Thanks
I created a Union Query for several linked Excel tables. Certain fields in the Excel table exceed 256 characters and Access (rightfully so) assigns these fields as "Memo". I have create a report based upon the Union Query; however, it will truncate the "Memo" fields to 255 (or 256 characters).
As a side note, if I create a report based upon a simple query using only one of the linked tables, it does not truncate the field.
Any suggestions on what maybe causing this truncation issue?
I have a table that contains a memo that is delimited by line breaks. For each of these breaks i need a new record in the query results that i can then use in a Labelling application.
My current query looks something like:
ID | Product | Pack Size
1 | item a | 1x1000,1x1050
2 | item b | 1x1000,20x25
3 | item a | 1x1000
(Where the , is a new line)
Whereas my ideal output is:
ID | Product | PackSize
1 | Item a | 1x1000
1 | Item a | 1x1050
2 | Item b | 1x1000
2 | Item b | 20x25
etc.
I think im supposed to use the Split() Function though i dont have a clue wherw to start.
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.
Running 2007
I never ran into this problem before.
I have a qry, with showing totals (group by). One of the fields is called comments, which is a memo field. I am only seeing partial comments.
When I redesign the query and take out the "totals", I see the entire comments.
Is there a fix to this?
I can select memo field (configured as RichText) as rendered html:
select somehtmlfield from sometable
And have rendered html output:
"normal bold"
Or select it as html string
select Cstr(somehtmlfield) from sometable
And get
"<div>normal <b>bold</b></div>
Is there any way to produce rendered html from string?
I would like to:
select SomeConvertingFunc("<div>something <b>bold</b></div>") from sometable
And have it rendered to "something bold".
I'm trying to concatenate two text fields into a memo field using an expression in a select query. My problem is that the text fields together end up more than 255 characters, so I need the resulting field to be a memo instead. I can't change the underlying text fields to memo fields because this is a large database used by others who need those fields to be text.
View 3 Replies View RelatedTable shows memo text fine..
but when i run a query its shows the memo field with text like: 였A and 愊` and 謍�
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 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 am in the process of changing over a text field to a memo field to generate more space (in 5 different databases :eek: ) Thanks to all the good info on several searches of this forum, I am pretty clear on how to do that.
The question is... when I convert a field that already has information in it, will I lose the current information?
TIA,
Darnys
when i change records, my subform that has a memo field, shows the memo field data is "highlighted". i can make a mistake and hit any keyboard key and the memo field data will get erased.
is there a way to prevent such a problem?
if not, is there a work around. eg, hide the memo field until needed for more input? ideas on both issues?
I am using
DoCmd.OutputTo to export a query but this truncates the memo fields, but in the db window,right clicking and choosing to export does not truncate the data.
How do I code to output all the data without truncating?
Hi guys and Girls
I have a txt box on a report which should show comments..
In the background table the field this txt box is bound to has a data type of memo. Yet the txt box on the report stops showing any more than 255 characters! any clues? I know 255 is the field size set for my Txt fields but memo fields should be able to display 65000000000000000 trillion characters with no problems.
When I look at the table the memo field has all the txt in it, it just wont display all the txt on the report.
Thanks for any help guys.