Finding Cells with a Paragraph

[kparadise]

Hello,
I have to download an Excel document from our SOR every week, and then I copy and paste it directly into my Access database. There is one column in the Excel document which CAN contains multiple values which appears to have been entered after a paragraph (see below). Now this copies over to Access fine, but what I am trying to do is create a query in Access which will say; give me all the ID's that contains a Fruit column which a paragraph make (basically, give me all the IDs that have more than one fruit in it).

The character which separates the multiple values is a RETURN within excel and when I copy it over to Access. I found this by simply copy/pasting the cells to Word and then showing the paragraph symbols.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Fruit[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Banana[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Apple
Orange
Kiwi
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Banana
Kiwi
[/TD]
[/TR]
</tbody>[/TABLE]

 

[james_lankford]

try

select 
  ID, 
  replace(Fruit, Chr(13) & Chr(10), "-") as [Fixed Fruit] 
from 
  table1 
where 
(
  (
    InStr( 1, [table1].[Fruit], chr(10) ) > 0
  )
)

you might have to change the Chr(13) & Chr(10) to be just
Chr(13)
or just
Chr(10)