Convert a string with a field to a carriage return

[bscs1963]

I have a text field which contains a gift message. The data has "<br />" in the data which needs to be converted to a carriage return. There could be up to 5 of these within the field that need to be converted.

I tried a replace, not knowing what to repalce it with. I tried & vbcrlf & told me I had too much data.

I have been completely unsuccessful in making this work, and would apporeciate a solution if someone has it.

 

[SkipVought]

All you need is vbLf

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dhookom]

You can't use vbCrLf or vbLf in a query or control source. These constants are only available in code. You can replace the <br /> with Chr(13) & Chr(10) for a carriage return/linefeed.

Duane
Hook'D on Access
MS Access MVP

 

[bscs1963]

Thanks for the response. I am definitely getting closer.

I did the replace and the <br /> was replaced with a square symbol(the carraige return). The problem now is that it prints the data with the symbols, rather than the actual carriage return. This all prints on one line in an Access report.

If i copy the data, paste into notepad, and copy it back to the field, it is formatted with the carriage returns correctly, and prints as expected with multiple lines.

Is there a solution for this?

 

[vbajock]

Can you post some code? I usually just parse the <br/> out of the strings using a for/next loop and replace them with vbCrLf like skip is suggesting, load it into a string variable and then

Somecontrol.value = StrVariable

 

[MakeItSo]

Just a guess: have you set the "multiline" property to "true"?

Cheers,
MakeItSo

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[TheAceMan1]

How are ya bscs1963 . . .

I've found in the past that the [blue]sequence order[/blue] of chr(13) and chr(10) [blue]can make a difference[/blue]. As an example ... in the control source of a textbox on a form in 2003, I have:

[blue]="ABC" & Chr([purple][b]10[/b][/purple]) & Chr([purple][b]13[/b][/purple]) & "DEF"[/blue]

Opening the form reveals
[blue]ABCDEF[/blue]
... and the reverse

[blue]="ABC" & Chr([purple][b]13[/b][/purple]) & Chr([purple][b]10[/b][/purple]) & "DEF"[/blue]

shows
[blue]ABC
DEF[/blue]

I have no idea why it happens this way, but the point is made.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[PaulF]

I created a table with 2 fields, DataIn & DataOut. I had info in DataIn that contained <br /> and I ran this code to populate DataOut with a Carriage Return/Line Feed instead of the <br /> and then when I printed the data, it printed out fine in a report.

Dim db As DAO.Database, rst As DAO.Recordset
Dim strOut As String
Set db = CurrentDb
Set rst = db.OpenRecordset("tblReplace")
With rst
.MoveFirst
Do While Not .EOF
strOut = Replace(rst!DataIn, "<br />", vbCrLf)
rst.Edit
rst!DataOut = strOut
rst.Update
.MoveNext
strOut = ""
Loop
End With
MsgBox "Done"
Set db = Nothing
Set rst = Nothing


PaulF

 

[dhookom]

I wouldn't use a recordset when a query would be much more efficient:

UPDATE YourTableName 
SET [YourFieldName] = Replace([YourFieldName],"<br />",Chr(13) & Chr(10));

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

PaulF . . .

Cleaning up your code a little reveals ...

[blue]   Dim db As DAO.Database, rst As DAO.Recordset
   
   Set db = CurrentDb
   Set rst = db.OpenRecordset("tblReplace")
   
   With rst
      .MoveFirst
      
      Do While Not .EOF
         .Edit
         !DataOut = Replace(rst!DataIn, "<br />", vbCrLf)
         .Update
         .MoveNext
      Loop
   End With
   
   MsgBox "Done"
   Set rst = Nothing
   Set db = Nothing[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]