Access Query Select all before a line break

Dear Experts

How do I trim a query so that it shows only the text before a line break / carriage return.  Also it would need to be able to show all text if there wasn't a line break / carriage return in the text

Can anybody help?
correlateAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Define the column as:

IIF(Instr([<fieldname>],chr$(13))>0, Left$([<fieldname>],Instr([<fieldname>],chr$(13))-1,[<fieldname>])

 Putting in the correct field name in place of <fieldname>.

Jim.
0
correlateAuthor Commented:
Hi Jim,

Thanks for this, unfortunately I'm getting an error "The expression you entered has a function containing the wrong number of arguments".

the code I am using is ...

Progress Notes1:IIF(Instr([Progress Notes],chr$(13))>0, Left$([Progress Notes],Instr([Progress Notes],chr$(13))-1,[Progress Notes])

Any ideas?
0
IrogSintaCommented:
You need 1 more closing parenthesis:

Progress Notes1:IIF(Instr([Progress Notes],chr$(13))>0, Left$([Progress Notes],Instr([Progress Notes],chr$(13))-1),[Progress Notes])

Ron
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Sorry, wasn't thinking.  IIF() evaluates both the TRUE and FALSE arguments.

Unless the string contains a carriage return, the TRUE argument always fails.

You need to define a procedure:


Function ReturnFirstLine(strInputString as string) as String

  If Instr(strInputString , chr$(13))>0 then
       ReturnFirstLine = left$(strInputString, Instr(strInputString , chr$(13))-1)
  Else
     ReturnFirstLine  = strInputString
  End If

End Function

 Paste that into a module and then in the query:

Progress Notes1: ReturnFirstLine([Progress Notes])

  As an aside, there is code floating around that is a bit more generic, which is a procedure to return the nth token (1st, 2nd, 3rd, etc chunk) based on a delimiter.

 I'd post what I have, but it's from the Access Developers Handbook, published by Sybex, so it's copy righted.

  I'll see if I can find some for you.

Jim.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Here you go:

http://msdn.microsoft.com/en-us/library/office/aa155763(v=office.10).aspx

With a slight modification, you can have it simply return the nth element of the array rather then the array.

 I'd call it something other then Split() though, which is a built-in function.

Jim.
0
Patrick MatthewsCommented:
Actually, no need at all to use VBA to protect against the "what if there is no line break?" contingency.  Indeed, there is not even a need to test for the presence of a carriage return character.  Simply do this:

ProgressNotes1:Left([Progress Notes], InStr(1, [Progress Notes] & Chr(13), Chr(13)) - 1)

It works because the concatenation ensures that there is always a carriage return to be found.

BTW, in case you also have to be on guard against line feeds, which can happen if you imported data from Excel:

ProgressNotes1:Left([Progress Notes], InStr(1, Replace([Progress Notes], Chr(10), Chr(13)) & Chr(13), Chr(13)) - 1)

:)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
correlateAuthor Commented:
Thanks for your help - they all work, I am going with a slightly modified version of ...

ProgressNotes1:Left([Progress Notes], InStr(1, Replace([Progress Notes], Chr(10), Chr(13)) & Chr(13), Chr(13)) - 1) as the datasource does come in from excel & better to be safe than sorry

The slight modification was to put a wrapper around it to deal with blanks (other wise blanks in [Progress Notes] came up with error#
0
Patrick MatthewsCommented:
Glad to help :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.