Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-13-2019, 09:15 PM   #1
Disabledg
Newly Registered User
 
Join Date: Nov 2019
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Disabledg is on a distinguished road
Access 97 remove line breaks

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.

Disabledg is offline   Reply With Quote
Old 11-13-2019, 09:25 PM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,558
Thanks: 58
Thanked 1,433 Times in 1,414 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Access 97 remove line breaks

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...
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 11-13-2019, 09:29 PM   #3
Disabledg
Newly Registered User
 
Join Date: Nov 2019
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Disabledg is on a distinguished road
Re: Access 97 remove line breaks

Quote:
Originally Posted by theDBguy View Post
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

Disabledg is offline   Reply With Quote
Old 11-13-2019, 09:40 PM   #4
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,720
Thanks: 93
Thanked 1,712 Times in 1,585 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Access 97 remove line breaks

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 11-13-2019, 11:11 PM   #5
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,301
Thanks: 115
Thanked 3,091 Times in 2,809 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Access 97 remove line breaks

Can you convert this to a newer file format e.g. 2003 MDB or convert to ACCDB?
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 11-13-2019, 11:18 PM   #6
Disabledg
Newly Registered User
 
Join Date: Nov 2019
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Disabledg is on a distinguished road
Re: Access 97 remove line breaks

Quote:
Originally Posted by isladogs View Post
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.
Disabledg is offline   Reply With Quote
Old 11-13-2019, 11:41 PM   #7
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,301
Thanks: 115
Thanked 3,091 Times in 2,809 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Access 97 remove line breaks

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.

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 11-13-2019, 11:47 PM   #8
Disabledg
Newly Registered User
 
Join Date: Nov 2019
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Disabledg is on a distinguished road
Re: Access 97 remove line breaks

Quote:
Originally Posted by isladogs View Post
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 by Disabledg; 11-14-2019 at 12:00 AM.
Disabledg is offline   Reply With Quote
Old 11-14-2019, 12:07 AM   #9
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,301
Thanks: 115
Thanked 3,091 Times in 2,809 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Access 97 remove line breaks

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

Did you try the approach suggested in post #4?
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 11-14-2019, 03:53 AM   #10
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,558
Thanks: 58
Thanked 1,433 Times in 1,414 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Access 97 remove line breaks

Quote:
Originally Posted by Disabledg View Post
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)
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 11-14-2019, 09:04 PM   #11
Disabledg
Newly Registered User
 
Join Date: Nov 2019
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Disabledg is on a distinguished road
Re: Access 97 remove line breaks

Quote:
Originally Posted by isladogs View Post
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.

Quote:
Originally Posted by theDBguy View Post
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".
Disabledg is offline   Reply With Quote
Old 11-14-2019, 09:10 PM   #12
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,558
Thanks: 58
Thanked 1,433 Times in 1,414 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Access 97 remove line breaks

Quote:
Originally Posted by Disabledg View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 11-14-2019, 09:17 PM   #13
Disabledg
Newly Registered User
 
Join Date: Nov 2019
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Disabledg is on a distinguished road
Re: Access 97 remove line breaks

Quote:
Originally Posted by theDBguy View Post
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?
Disabledg is offline   Reply With Quote
Old 11-14-2019, 09:28 PM   #14
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,558
Thanks: 58
Thanked 1,433 Times in 1,414 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Access 97 remove line breaks

Quote:
Originally Posted by Disabledg View Post
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...
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 11-14-2019, 09:42 PM   #15
Disabledg
Newly Registered User
 
Join Date: Nov 2019
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Disabledg is on a distinguished road
Re: Access 97 remove line breaks

Quote:
Originally Posted by theDBguy View Post
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 is offline   Reply With Quote
Reply

Tags
access97 , linebreak

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Remove Carriage Returns/Line Breaks in Field As Soon As Pasted. zashaikh Forms 2 01-04-2018 07:26 AM
Code to remove line breaks in table reedhillltd General 4 06-22-2014 08:51 AM
Trim/Remove line breaks from every field in every record NeutronFlux Modules & VBA 8 12-06-2012 01:07 PM
Remove Line Breaks Dmak Modules & VBA 2 09-25-2012 06:40 AM
Question How to remove line breaks from a table? mark_m General 2 02-23-2009 05:23 AM




All times are GMT -8. The time now is 01:20 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World