 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / November 2005truncating memo field in query result
As part of a query I would like to create a "field" that displays only the first line of a memo field in a table. The first line of the memo field is the title of a report. I would like this to be displayed instead of a seperate field which is the report number. Is there a way to limit, through truncation or something, the return to a single line? Is the "First line" terminated with a new line character or is it some specific number of characters? I would try a calculated field like the following Field: TitleLine: IIF([MemoField] is Not Null,LEFT([MemoField],Instr(1,[MemoField] & Chr(13),Chr(13))-1)) > As part of a query I would like to create a "field" that displays only > the first line of a memo field in a table. [quoted text clipped - 4 lines] > Is there a way to limit, through truncation or something, the return to > a single line? The first line is a title that can vary from short to long. It is in a memo field so can be as long as the author wants. I'm not sure if this answers your question but when it is entered, it should have a hard return before continuing with the rest of the text in the field. As far as your suggestion goes, I would like to understand your solution. I interpret this as saying "TitleLine" is the name of the field? Then as long as something is in the memo field, it will take the leftmost 13 characters. What does "instr" do? What is the "-1" for? Thanks for your help, John. Field: TitleLine: IIF([MemoField] is Not Null,LEFT([MemoField],Instr(1,[MemoField] & Chr(13),Chr(13))-1)) TitleLine is a name for the calculated field, you can make it anything you want. Chr(13) is a carriage return and I probably should have been testing for Chr(10) a line feed InStr is a function that looks IN a STRing for another string and returns the starting position of the string it is looking for. The -1 means decrease the result of the InStr function by 1 - so the last character is not included in when the LEFT function returns a number of characters. So what I should have posted Field: TitleLine: IIF([MemoField] is Not Null, LEFT([MemoField],Instr(1,[MemoField] & Chr(13) & Chr(10),Chr(13) & Chr(10))-1)) If your MemoField always has a hard return if there is any text in it, then you could simplify the above by removing the Chr(13) & Chr(10) I added to the MemoField to make sure there would be a return in the string we were examining with InStr. > The first line is a title that can vary from short to long. It is in a > memo field so can be as long as the author wants. I'm not sure if this [quoted text clipped - 8 lines] > > Thanks for your help, John. This works great! Thanks. The "-1" why don't you want to include the last character - because it is a hard return? Because you will end up with an unneeded character AND it will probably show up as a small square on the screen. > This works great! Thanks. > The "-1" why don't you want to include the last character - because it > is a hard return? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.