UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Adding Line Breaks To Records Using Vba, Access 2016    
 
   
vba.php
post Feb 28 2020, 11:30 AM
Post#1



Posts: 28
Joined: 28-November 19



hello folks,

I haven't seen any resolution to this issue anywhere online. I have the following data in some of my records:

CODE
display: block;margin-top: 1em;margin-bottom: 1em;margin-left: 40px;margin-right: 40px;


I have tried many incarnations of this concept using VBA, but they all place double line breaks in the fields. Here is some of the code I've tried:

CODE
1) strOut = strOut & Trim(vals(x)) & ";" & vbNewLine
2) strOut = strOut & Trim(vals(x)) & ";" & vbcrlf
3) strOut = strOut & Trim(vals(x)) & ";" & vbcr

and every one of those results in THIS:

CODE
display: block;

margin-top: 1em;

margin-bottom: 1em;

margin-left: 40px;

margin-right: 40px;
can anyone here tell me if there is a solution to this problem, whereby I only get a SINGLE break?? thanks!
Go to the top of the page
 
GroverParkGeorge
post Feb 28 2020, 11:36 AM
Post#2


UA Admin
Posts: 36,962
Joined: 20-June 02
From: Newcastle, WA


Welcome to UtterAccess.

Can we see the ENTIRE VBA module where you are trying to do this?

I have never experienced this particular problem using the vbCrLf constant. Perhaps we can spot something if we see everything in that VBA function.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
cheekybuddha
post Feb 28 2020, 11:36 AM
Post#3


UtterAccess Moderator
Posts: 12,601
Joined: 6-December 03
From: Telegraph Hill


welcome2UA.gif

Are you importing this back to a Unix-type system?

If so, just use vbLf as your separator.

It will still appear as a single line on Windows, but will only have a single linebreak on a normal*nix system

--------------------


Regards,

David Marten
Go to the top of the page
 
vba.php
post Feb 28 2020, 11:38 AM
Post#4



Posts: 28
Joined: 28-November 19



ok you guys, this is very short. here you go:

CODE
Function t()

Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("1")
Dim vals() As String
Dim fld As String
Dim strOut As String
Dim x As Long

rs.MoveLast
rs.MoveFirst
    With rs
        Do Until .EOF
            fld = .Fields(1)
                strOut = ""
                    If InStr(fld, ";") < Len(Trim(fld)) And InStr(Trim(fld), ";") <> 0 Then
                        vals = Split(fld, ";")
                            For x = LBound(vals) To UBound(vals)
                                strOut = strOut & Trim(vals(x)) & ";" & vbNewLine
                            Next x
                        Debug.Print .Fields(0) & " - " & Left(strOut, Len(strOut) - 1)
                        .Edit
                        .Fields(1) = Left(strOut, Len(strOut) - 1)
                        .Update
                    End If
            .MoveNext
        Loop
    End With

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

End Function
Go to the top of the page
 
GroverParkGeorge
post Feb 28 2020, 11:43 AM
Post#5


UA Admin
Posts: 36,962
Joined: 20-June 02
From: Newcastle, WA


Ah, you're pulling rows from an array, vals().

I recently had the same problem with an array based process. If I can find it, I'll post something from it. As I recall, though, I ended up using Replace() on the final string to replace the double line breaks with a single one as a workaround. Let me see what I can find.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Feb 28 2020, 11:45 AM
Post#6


UA Admin
Posts: 36,962
Joined: 20-June 02
From: Newcastle, WA


Nope, I was wrong about the solution. I ended up using the one suggested, vbLf . The attempt to do a Replace() was an interim attempt that didn't work out.
This post has been edited by GroverParkGeorge: Feb 28 2020, 11:46 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
cheekybuddha
post Feb 28 2020, 11:53 AM
Post#7


UtterAccess Moderator
Posts: 12,601
Joined: 6-December 03
From: Telegraph Hill


CODE
Function t()

  Dim strSQL As String

  strSQL = "UPDATE [1] SET YourField = Replace(YourField, ';', ';' &  Chr(10));"
  CurrentDb.Execute strSQL, dbFailOnError

End Function


*** Replace YourField with proper fieldname ***

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
vba.php
post Feb 28 2020, 11:57 AM
Post#8



Posts: 28
Joined: 28-November 19



Grover,

you VBLF solution results in this, printed out:
CODE
margin-bottom: 1 em;

margin-left: 0;

margin-right: 0;

padding-left: 40px;
and the code being used is this:
CODE
                        Debug.Print .Fields(0) & " - " & Left(strOut, Len(strOut) - 1)
                        .Edit
                        .Fields(1) = Left(strOut, Len(strOut) - 1)
                        .Update
to be honest with you guys, I don't like access or office for that matter, due to issues like this. I would rather go with Python. Is this my issue that is unknown and I'm just not getting it?

chr(10) also causes the same issue.
This post has been edited by vba.php: Feb 28 2020, 11:58 AM
Go to the top of the page
 
cheekybuddha
post Feb 28 2020, 12:15 PM
Post#9


UtterAccess Moderator
Posts: 12,601
Joined: 6-December 03
From: Telegraph Hill


How have you populated your Access table?

Are you sure you don't already have a LF character after your semi-colons? (You won't see it in Windows)

--------------------


Regards,

David Marten
Go to the top of the page
 
vba.php
post Feb 28 2020, 12:21 PM
Post#10



Posts: 28
Joined: 28-November 19



that might just be an issue. there are spaces after some items in the fields and TRIM() doesn't get rid of them. I might have to do this by hand...
Go to the top of the page
 
cheekybuddha
post Feb 28 2020, 12:22 PM
Post#11


UtterAccess Moderator
Posts: 12,601
Joined: 6-December 03
From: Telegraph Hill


You may want to try:
CODE
Function t()

  Dim strSQL As String

  strSQL = "UPDATE [1] SET YourField = Replace(Replace(YourField, Chr(13),  Chr(10)), Chr(10) & Chr(10), Chr(10));"
  CurrentDb.Execute strSQL, dbFailOnError

End Function

--------------------


Regards,

David Marten
Go to the top of the page
 
vba.php
post Feb 28 2020, 12:24 PM
Post#12



Posts: 28
Joined: 28-November 19



thank you so much guys! after all of this, the solution, right or wrong, is this:

CODE
                    If InStr(fld, ";") < Len(Trim(fld)) And InStr(Trim(fld), ";") <> 0 Then
                        vals = Split(fld, ";")
                            For x = LBound(vals) To UBound(vals)
                                strOut = strOut & Trim(vals(x)) & ";" & vbCrLf
                            Next x
                        strOut = Replace(strOut, vbCrLf & ";", "")
                        strOut = Left(strOut, Len(strOut) - 1)
                        strOut = Left(strOut, Len(strOut) - 1)
                        .Edit
                        .Fields(1) = strOut
                        .Update
                    End If


a little awkward, no doubt, but regardless it works fine.
This post has been edited by vba.php: Feb 28 2020, 12:25 PM
Go to the top of the page
 
GroverParkGeorge
post Feb 28 2020, 12:29 PM
Post#13


UA Admin
Posts: 36,962
Joined: 20-June 02
From: Newcastle, WA


Congratulations on solving the problem.

I agree that the inability to "see" hidden control characters makes string manipulation a bit hazy sometimes. Whatever works.

Continued success with the project.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
vba.php
post Feb 28 2020, 12:33 PM
Post#14



Posts: 28
Joined: 28-November 19



thanks Grover. I hate posting issues on forums like this because I'm an issue solver myself. so asking for help from other people when the problem really is the programs we're working with is always extremely annoying. which is why I gave up access long ago and went with the web languages and python. =(
Go to the top of the page
 
cheekybuddha
post Feb 28 2020, 12:33 PM
Post#15


UtterAccess Moderator
Posts: 12,601
Joined: 6-December 03
From: Telegraph Hill


Well done on finding a solution! thumbup.gif

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Feb 28 2020, 12:35 PM
Post#16


UtterAccess Moderator
Posts: 12,601
Joined: 6-December 03
From: Telegraph Hill


As an additional note, a good text editor will help in this situation

--------------------


Regards,

David Marten
Go to the top of the page
 
GroverParkGeorge
post Feb 28 2020, 12:35 PM
Post#17


UA Admin
Posts: 36,962
Joined: 20-June 02
From: Newcastle, WA


hmm ... I can't let this slide without a comment. "I don't like access or office for that matter, due to issues like this. I would rather go with Python."

Access is a set of development tools that includes interface objects (i.e. the forms and reports), a database engine (i.e. ACE) and logic objects (i.e. VBA). In other words, Access is a complete relational database application development tool set. The part you are comparing to Python is, to be strictly accurate, only that last element, the coding language. So, while Python is a more modern way to manage your data than VBA, it's only one part of the whole. We all agree, I think, that VBA is long in the tooth, but it has the advantage of decades of experience behind it....

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
cheekybuddha
post Feb 28 2020, 12:36 PM
Post#18


UtterAccess Moderator
Posts: 12,601
Joined: 6-December 03
From: Telegraph Hill


Hmmm... this issue is more to do with how Windows deals with line-endings compared with most other OS's

--------------------


Regards,

David Marten
Go to the top of the page
 
vba.php
post Feb 28 2020, 12:41 PM
Post#19



Posts: 28
Joined: 28-November 19



QUOTE
Access is a set of development tools that includes interface objects (i.e. the forms and reports), a database engine (i.e. ACE) and logic objects (i.e. VBA). In other words, Access is a complete relational database application development tool set. The part you are comparing to Python is, to be strictly accurate, only that last element, the coding language. So, while Python is a more modern way to manage your data than VBA, it's only one part of the whole. We all agree, I think, that VBA is long in the tooth, but it has the advantage of decades of experience behind it....
unfortunately you guys, I'm in the business of solving problems and I get paid for it. I don't really do a lot of deep level knowledge work like knowing the little quirks of access and windows inside and out. that work is for cubicle dwellers at large corporations. well, maybe other folks too. I probably should get back into that work instead of doing what I do. maybe I don't because doing this is so much fun and I can keep my soul in tact instead of being stripped away by politics and greed. I really don't know.

I have no issue with VBA either, but I have had numerous problems with access for 20 years now regarding it not being predictable and changing it's bug ridden issues from version to version.
Go to the top of the page
 
GroverParkGeorge
post Feb 29 2020, 11:52 AM
Post#20


UA Admin
Posts: 36,962
Joined: 20-June 02
From: Newcastle, WA


Continued success with your projects.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    28th March 2020 - 10:22 AM