truncating memo field in query result

  • Thread starter Thread starter papa jonah
  • Start date Start date
P

papa jonah

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))
 
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.
 
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.
 
Back
Top