Single String to Multiple String on LineBreak (1 Viewer)

philbullock1223 · Aug 24, 2012

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 · Aug 24, 2012

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 · Aug 24, 2012

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;

Code:

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 · Aug 24, 2012

I would have expected something more simple:

Code:

="**" & 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 · Aug 24, 2012

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 · Aug 24, 2012

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

philbullock1223 · Aug 24, 2012

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 · Aug 24, 2012

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 · Aug 24, 2012

@vbaInet

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

Code:

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

philbullock1223 · Aug 24, 2012

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 · Aug 24, 2012

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 · Aug 24, 2012

Beetle said:
@vbaInet

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

Code:

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

Good you got it working Beetle.

Single String to Multiple String on LineBreak (1 Viewer)

philbullock1223

Registered User.

vbaInet

AWF VIP

Beetle

Duly Registered Boozer

vbaInet

AWF VIP

philbullock1223

Registered User.

vbaInet

AWF VIP

philbullock1223

Registered User.

vbaInet

AWF VIP

Beetle

Duly Registered Boozer

philbullock1223

Registered User.

vbaInet

AWF VIP

vbaInet

AWF VIP

Similar threads

Users who are viewing this thread