Single String to Multiple String on LineBreak

[philbullock1223]

I have a pretty simple question I can't see to find the answer to.

The user has the ablity to write notes on many lines (memo) that are stored in a table.

When I output to a text file I need a "**" to be placed before each line of the notes.

I have succeeded with the first line easily by:

Print #FileNbrExport, "** " & TABLE![Notes]

But it outputs my notes like this:

** Here is the first line
Here is the second line
Here is the third line

I need it to output my notes like this:
** Here is the first line
** Here is the second line
** Here is the third line

Any suggestions?

 

[vbaInet]

If that's how you want it displayed you are better off making those changes permanent in the field because you have many lines. However, tell us what the Default View of your form is. Is it a Single or Continuous Form?

 

[Beetle]

Not sure if this is the most efficient solution or not (may depend on how many lines we're talking about in the field) but given a memo field named MyText with the following data;

This is the first line
This is the second line
This is the third line

This;

Dim vArray As Variant
Dim strNotes As String
    
vArray = Split(Me!MyText, Chr(10))
For i = 0 To UBound(vArray)
    strNotes = strNotes & "**" & vArray(i)
Next i
    
Debug.Print strNotes

Returns;

**This is the first line
**This is the second line
**This is the third line

 

[vbaInet]

I would have expected something more simple:

="**" & Replace([[COLOR=Red]MemoField[/COLOR]], Chr(13), Chr(13) & Chr(10) & "**")

... but we still need to know the Default View and how many lines on average you will be performing this on.

 

[philbullock1223]

Beetle, I implimented your code and it works great. It might not be the simplest, but for me it works!

VbaInet, the lines (on average) is unknown, so the loop provided by Beetle works for me.

Thanks for all the help!

 

[vbaInet]

And did you test what I gave you in my post? Still haven't told us what view your form is in either?

 

[philbullock1223]

vbaInet, I did not test your code. The code provided seems to be for a textbox within a form and not VBA code.

The form I am using is not in a view (design view, layout view, etc.)... it is simply open.

Maybe I am missing something.......? My apologies, I simply dabble in VBA and am not a professional coder.

Phil

 

[vbaInet]

It can be used in a query (without the equal to sign) or in a form or a report. You don't have to test it, I was just wondering if you did.

Look at the Default View property of your form. You will see whether it's set to one of Continuous Form, Datasheet or Single Form.

 

[Beetle]

@vbaInet

Thanks for the simpler solution. Seems to work correctly on the previous example data if modified like;

= "**" & Replace([MemoField], Chr(10), Chr(10) & "**")

 

[philbullock1223]

Ah! I am using the form in single form view. It is not a datasheet form.

I have not tested the query code, but I appreciate the response. I will keep it in my pocket!

 

[vbaInet]

The only reason why I ask whether it's in Datasheet, Continuous or Single is because it will take longer to do if you were in any view other than Single. And if this was the case, I would have advised you to make those changes permanent (if possible).

Happy developing!

 

[vbaInet]

@vbaInet

Thanks for the simpler solution. Seems to work correctly on the previous example data if modified like;

= "**" & Replace([MemoField], Chr(10), Chr(10) & "**")

Good you got it working Beetle.