Question Access 97 remove line breaks (1 Viewer)

Disabledg

New member
Local time
Today, 20:16
Joined
Nov 14, 2019
Messages
9
Hello! I have old mdb base with invisible line break. After export to excel i got new lines - and it brokes data structure.

How can i remove line breaks from datatable?

UPDATE table1 SET attr1 = trim(attr1) dont help.
UPDATE table1 SET attr1 = replace([attr1 ], chr(10), " ") not working - replace function not exist in access 97.
 

theDBguy

I’m here to help
Local time
Today, 10:16
Joined
Oct 29, 2018
Messages
8,785
Hi. Welcome to AWF!

Just a guess, but let's say you have a word as data but terminated by a line break. If so, try this.

Code:
UPDATE TableName SET FieldName=Left(FieldName, Len(FieldName)-2)
Hope that helps...
 

Disabledg

New member
Local time
Today, 20:16
Joined
Nov 14, 2019
Messages
9
Hi. Welcome to AWF!

Just a guess, but let's say you have a word as data but terminated by a line break. If so, try this.

Code:
UPDATE TableName SET FieldName=Left(FieldName, Len(FieldName)-2)
Hope that helps...
Line break not on all lines. So if i cut data for 2 chars - i broke data :(
 

The_Doc_Man

Happy Retired Curmudgeon
Local time
Today, 12:16
Joined
Feb 28, 2001
Messages
16,315
Depending on how you got the data into the table, those breaks might be what is defined by VBA constant vbCrLf (a carriage return AND line feed). If worst comes to worst, what you might have to do is write up a function that takes a line as input and scans the characters one at a time with the MID function. When you hit vbCR or vbLF, drop in a space; otherwise, drop in what you found. But you would have to be prepared for this to not be a fast query.

In a general module:

Code:
Public Function StripCRLF( sInput As String ) As String

DIM sInArg As String, lInLen As Long, sOutArg As String, sNxtChr As String
DIM lPos As Long

    sInArg = sInput
    lInLen = LEN( sInArg )
    sOutArg = ""
    IF lInLen > 0
      Then
        FOR lPos = 1 TO lInLen
            sNxtChr = MID( sInArg, lPos, 1 )
            SELECT CASE sNxtChr
                CASE vbCR, vbLF
                    sOutArg = sOutArg & " "        '(or do nothing at all here)
                CASE ELSE
                    sOutArg = sOutArg & sNxtChr
            END SELECT
        NEXT lPos
      End If
    StripCRLF = sOutArg
End Function
If that is in a general module, DoCmd.RunSQL can run a query that uses that function on any string for which you need to strip out CR and LF. THIS WILL NOT BE FAST. But it should be OK. TEST it first. Perhaps make a copy of the whole DB BEFORE you try to run it. What you need SHOULD be similar to what I just showed you.
 

isladogs

CID Moderator
Staff member
Local time
Today, 18:16
Joined
Jan 14, 2017
Messages
12,589
Can you convert this to a newer file format e.g. 2003 MDB or convert to ACCDB?
 

isladogs

CID Moderator
Staff member
Local time
Today, 18:16
Joined
Jan 14, 2017
Messages
12,589
Ah but if you have a later version, it can read the A97 file without conversion. In fact any version up from A2000 to A2010 can do so.
You can then use Replace to eliminate the line breaks but the file will still be in A97 format.
 

Disabledg

New member
Local time
Today, 20:16
Joined
Nov 14, 2019
Messages
9
Ah but if you have a later version, it can read the A97 file without conversion. In fact any version up from A2000 to A2010 can do so.
You can then use Replace to eliminate the line breaks but the file will still be in A97 format.
I try it. Execute UPDATE table1 SET attr1 = replace([attr1 ], chr(10), " ") shows warning "undefined function CHR in expression".
 
Last edited:

isladogs

CID Moderator
Staff member
Local time
Today, 18:16
Joined
Jan 14, 2017
Messages
12,589
In which Access version did you try this?
Also remove the space in [attr1 ].

Did you try the approach suggested in post #4?
 

theDBguy

I’m here to help
Local time
Today, 10:16
Joined
Oct 29, 2018
Messages
8,785
Line break not on all lines. So if i cut data for 2 chars - i broke data :(
Hi. Continuing with my original assumption:

Code:
UPDATE TableName 
SET FieldName=Left(FieldName,Len(FieldName)-2)
WHERE Right(FieldName,2)=Chr$(13) & Chr$)10)
 

Disabledg

New member
Local time
Today, 20:16
Joined
Nov 14, 2019
Messages
9
In which Access version did you try this?
Also remove the space in [attr1 ].

Did you try the approach suggested in post #4?
Possible im stupid and do not understand how to use it(. Acess 2016.

Hi. Continuing with my original assumption:

Code:
UPDATE TableName 
SET FieldName=Left(FieldName,Len(FieldName)-2)
WHERE Right(FieldName,2)=Chr$(13) & Chr$)10)
"undefined function Left in expression".
 

theDBguy

I’m here to help
Local time
Today, 10:16
Joined
Oct 29, 2018
Messages
8,785
Possible im stupid and do not understand how to use it(. Acess 2016.



"undefined function Left in expression".
Try Left$(). Sorry, I don't have a copy of Access 97 to find out which functions are available to you.
 

Disabledg

New member
Local time
Today, 20:16
Joined
Nov 14, 2019
Messages
9
Try Left$(). Sorry, I don't have a copy of Access 97 to find out which functions are available to you.
Now i try it in access 2016.
Left$ not help. From query wizard i see all this functions. Maby it can't be used with old mdb format?
 

theDBguy

I’m here to help
Local time
Today, 10:16
Joined
Oct 29, 2018
Messages
8,785
Now i try it in access 2016.
Left$ not help. From query wizard i see all this functions. Maby it can't be used with old mdb format?
What happened? Did you get the same error? Just curious...
 

Disabledg

New member
Local time
Today, 20:16
Joined
Nov 14, 2019
Messages
9
What happened? Did you get the same error? Just curious...
Yes. The same error. Undefined function left $ in expression. I google it and find some material about utility.mda - in my vb -> tools -> references -> utility.mda is missing. Maby it matter?
 

Disabledg

New member
Local time
Today, 20:16
Joined
Nov 14, 2019
Messages
9
Yeeeees. I found utility.mda for windows xp on internet, copy it to office/1033/utility.mda and UPDATE table1 SET attr1 = replace([attr1 ], chr(10), " ") works!
 
Last edited:

Disabledg

New member
Local time
Today, 20:16
Joined
Nov 14, 2019
Messages
9
Next Question. Where can be found Access runtime command line properties? I want to make it auto.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom