Access 2003 Database Removeal of CR

stewartrose

Registered User.
Local time
Today, 09:30
Joined
Aug 11, 2003
Messages
16
Good Morning Team,

I have a little problem, I have a access database that appears to have square boxs in some of the feilds, I presume these are Cr/lf, how the hell do I get rid of them please..

Best regards from Alan
 
What kind of field and how many characters total in the field? They may *not* be CrLf characters but some other control code or otherwise unprintable character.
 
My guess is you have a string text field of some kind. Here's a couple of functions that together should allow you to strip any character you want from a string.
Code:
Public Function CleanString(strIn As String) As String
'-- Strip unwanted characters from the incoming string
Dim OffSet As Long
For OffSet = 1 To Len(strIn)
   CleanString = CleanString & FilterIt(Mid(strIn, OffSet, 1))
Next OffSet

End Function

Public Function FilterIt(strIn As String) As String

Select Case strIn

   Case vbCr
      FilterIt = strIn     '-- Allow a Carriage Return
   Case vbLf
      FilterIt = strIn     '-- Allow a Line Feed
   Case Is < " "
      FilterIt = ""        '-- Turn all other Control Codes to Zero Length String
   Case Chr(127)
      FilterIt = ""        '-- Remove DEL as well
   Case Else
      FilterIt = strIn     '-- Pass back everything else
   
End Select

End Function
 
Hi Team,
The Feild is a Blog with some 300 words in it, and 21000 records, which I want to export into a tab delimitered file to export into a mysql database.
The Character that you see when you open the access database is 2 square boxes but the center of the box is white, and when I try to import it into a mysql databse,the first 5 feilds get filled in, and the rest are blank, and the only character I can find that is stopping this from working is those sqaure boxs..

Your code for stripping looks great, but as a beginner to Access 2003 what do I do with the code please, he he keep it clean lads...

All the best from Alan [ And Thanks ]
 
Put the code supplied in a standard module named basFunctions and then it can be called from anywhere. You could put it in an Update query so it would just clean up that single field.
UPDATE YourTable SET YourTable.YourField = CleanString([YourField]);
 
Hi RuralGuy,

This may be a pain, but from the query side of things I have no idea what to do never done anything like this before, if you have the time i would be glad of a step by step guide please, but only if you have time...

All the best from Alan
 
Start by using the query builder to make a select query that pulls the field you need filtered.
 
Hi RuralGuy,

Well I think I am moving forward, I put you code in a module and named it basFunctions, then opened up a query in design mode added the feilds for the table used in this case Articles, then selected Query from top menu, and selected SQL Specific, then Data Definition, added UPDATE Articles SET Articles.PreviewText = CleanString([PreviewText]);
and saved it as Query..then clicked on it and it ran, when though all records and said it was done, checked on the table but it had not removed the squares, have I missed something...

All the best from Alan
 
Real close. I'm not sure what doing it that way accomplishes. Instead of selecting SQL specific, select UPDATE query. Then View SQL View and replace everything with UPDATE Articles SET Articles.PreviewText = CleanString([PreviewText]);. Save it and run it. It is probably the same thing but try it anyway.
 
My test verified that your method accomplishes the same as mine. I hope you are working on a backup of the table so we can run some tests. We can also eliminate 8 bit characters in the FilterIt function.
 
Hi RuralGuy,

Yes I have a backup, learnt that leasson years ago at great expence...
What would you like me to try, or would you like me to email a database with a couple of records in it...

All the best from Alan
 
Go ahead and zip up the db with some records in it and post it here. I'd like to determine what the character is anyway.
 
It is unlikely to be a CrLf character, as access sees this as a new line and prints it out as such in all of its controls and tables. There are plenty of other things it could be though :D If you work out what it is, please post up ;)
 
Morning RuralGuy,

Thank you for the reply, with the wind blowing from the right direction, and the sun shinning from the north, I hope this upload worked never done this before..

Thank you from Alan
 

Attachments

Here you go I made a query called qryTest and it removes the square boxes in the query so you could make your tab delimited file from the query.
 

Attachments

  • t.ZIP
    t.ZIP
    36.2 KB · Views: 164
Alan,
I have to leave for a few hours. What ever is in those fields is not straight forward. I'll beat it up later and figure out what is going on. I wanted to give you notice in case you are on a tight schedule.
 
Ive run across this many times, especially with data imported from excel. Simply copy the square. Open find and replace, paste the square into Find leaving replace blank then select replace all. BTW the square is a line break
 
Hi Keith,

Mmmm very impressed, I can see that you have taken the data and filtered it then paste it into a new feild, I hope my understanding is correct using NewPreviewText: Right([PreviewText],Len([PreviewText])-2) now this assumes the data is awalys in the same place (I HOPE) how can you do it if the data is at the end of the line, and do you know what this charator is please


Best regards from Alan and Thank you..
 
Last edited:
Hi Keith,

Mmmm very impressed, I can see that you have taken the data and filtered it then paste it into a new feild, I hope my understanding is correct using NewPreviewText: Right([PreviewText],Len([PreviewText])-2) now this assumes the data is awalys in the same place (I HOPE) how can you do it if the data is at the end of the line, and do you know what this charator is please


Best regards from Alan and Thank you..
 
I didn't paste the data into new field I maniuplated the data in the field and gave it a new name. If the squares were at the end of string you could use the Left function instead of the Right function. I believe the square is a line break.
 

Users who are viewing this thread

Back
Top Bottom