11-13-2019, 09:15 PM
|
#1
|
Newly Registered User
Join Date: Nov 2019
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
|
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.
|
|
|
11-13-2019, 09:25 PM
|
#2
|
I’m here to help
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,558
Thanks: 58
Thanked 1,433 Times in 1,414 Posts
|
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.
|
|
|
11-13-2019, 09:29 PM
|
#3
|
Newly Registered User
Join Date: Nov 2019
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
|
Re: Access 97 remove line breaks
Quote:
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.
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
|
|
|
11-13-2019, 09:40 PM
|
#4
|
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
|
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.
|
|
|
11-13-2019, 11:11 PM
|
#5
|
High Noon Moderator
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,301
Thanks: 115
Thanked 3,091 Times in 2,809 Posts
|
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)
|
|
|
11-13-2019, 11:18 PM
|
#6
|
Newly Registered User
Join Date: Nov 2019
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
|
Re: Access 97 remove line breaks
Quote:
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.
|
|
|
11-13-2019, 11:41 PM
|
#7
|
High Noon Moderator
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,301
Thanks: 115
Thanked 3,091 Times in 2,809 Posts
|
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)
|
|
|
11-13-2019, 11:47 PM
|
#8
|
Newly Registered User
Join Date: Nov 2019
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
|
Re: Access 97 remove line breaks
Quote:
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".
Last edited by Disabledg; 11-14-2019 at 12:00 AM.
|
|
|
11-14-2019, 12:07 AM
|
#9
|
High Noon Moderator
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,301
Thanks: 115
Thanked 3,091 Times in 2,809 Posts
|
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)
|
|
|
11-14-2019, 03:53 AM
|
#10
|
I’m here to help
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,558
Thanks: 58
Thanked 1,433 Times in 1,414 Posts
|
Re: Access 97 remove line breaks
Quote:
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:
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.
|
|
|
11-14-2019, 09:04 PM
|
#11
|
Newly Registered User
Join Date: Nov 2019
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
|
Re: Access 97 remove line breaks
Quote:
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.
Quote:
Originally Posted by theDBguy
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".
|
|
|
11-14-2019, 09:10 PM
|
#12
|
I’m here to help
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,558
Thanks: 58
Thanked 1,433 Times in 1,414 Posts
|
Re: Access 97 remove line breaks
Quote:
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.
__________________
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.
|
|
|
11-14-2019, 09:17 PM
|
#13
|
Newly Registered User
Join Date: Nov 2019
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
|
Re: Access 97 remove line breaks
Quote:
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?
|
|
|
11-14-2019, 09:28 PM
|
#14
|
I’m here to help
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,558
Thanks: 58
Thanked 1,433 Times in 1,414 Posts
|
Re: Access 97 remove line breaks
Quote:
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...
__________________
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.
|
|
|
11-14-2019, 09:42 PM
|
#15
|
Newly Registered User
Join Date: Nov 2019
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
|
Re: Access 97 remove line breaks
Quote:
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?
|
|
|
Thread Tools |
|
Display Modes |
Rate This Thread |
Linear Mode
|
|
All times are GMT -8. The time now is 01:20 PM.
|
|