Question Access 97 remove line breaks

[Disabledg]

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]

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.

UPDATE TableName SET FieldName=Left(FieldName, Len(FieldName)-2)

Hope that helps...

[Disabledg]

Originally Posted by theDBguy

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.

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]

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:

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]

Can you convert this to a newer file format e.g. 2003 MDB or convert to ACCDB?

[Disabledg]

Originally Posted by isladogs

Can you convert this to a newer file format e.g. 2003 MDB or convert to ACCDB?

No(. It used by program what understand only old format.

[isladogs]

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]

Originally Posted by isladogs

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".

[isladogs]

In which Access version did you try this?
Also remove the space in [attr1 ].

Did you try the approach suggested in post #4?

[theDBguy]

Originally Posted by Disabledg

Line break not on all lines. So if i cut data for 2 chars - i broke data

Hi. Continuing with my original assumption:

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

[Disabledg]

Originally Posted by isladogs

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.

Originally Posted by theDBguy

Hi. Continuing with my original assumption:

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

"undefined function Left in expression".

[theDBguy]

Originally Posted by Disabledg

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]

Originally Posted by theDBguy

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]

Originally Posted by Disabledg

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]

Originally Posted by theDBguy

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?