need to remove breaks from paragraphs

[jnixon]

I'm having big problems exporting and converting data in Access. I have a large source of records that I want to manipulate and then export from Access into delimited text files. The problem is, I need to remove some spaces from a memo field. While the trim() function will remove a lot of spaces, it doesn't remove breaks/hard returns/whatever-you-call-it that breaks up the paragraphs.

Here's and example of what it looks like when I export it now:

SECTION: 37 BLOCK: 596 LOT: 5

ALL THAT CERTAIN PLOT,

I want it to look like this:

SECTION: 37 BLOCK: 596 LOT: 5 ALL THAT CERTAIN PLOT,

How do I get rid of the breaks? Thanks!!

 

[RuralGuy]

Replace(YourField,Chr(13),"") '-- This is vbCR
Replace(YourField,Chr(10),"") '-- This is vbLF

 

[jnixon]

Wow, could it be that easy?? Amazing, I think it worked! Thanks!!

 

[RuralGuy]

Glad to help!

 

[jnixon]

I'm actually having another problem with this now...

I'm pulling a field...a property Legal Description...it's a very long Memo field from a SQL database. At some point, after about 510-514 characters, the field just cuts off. I don't know why. The field is linked in the Access database to the SQL server. Any idea why this happens? Thanks!

 

[RuralGuy]

I really don't have a clue, sorry. Is there anything in the following link that sheds light on the problem?
http://www.accessforums.net/ftopic5593.html

 

[jnixon]

Well, no I don't think so...I'm not doing any of the things described in that thread. It's not a Union and it's not a Select Distinct, it's just a Select query pulling the data from a few different tables on the SQL server.

I think the problem may be that there are more Ascii characters that I need to get rid of. When I add another field to remove Asciii code 254 (found tables in Google, I notice that I can see some more words in the memo field that I didn't see before:

LegalDesc254: Replace([LegalDesc10],Chr(254),"")
**[LegalDesc10] is the field that removes character 10**

So before:

LegalDesc10:

A CERTAIN PARCEL OR LOT OF GROUND WITH BUILDINGS AND IMPROVEMENTS THEREON, AND ALL RIGHTS, WAYS, AND PRIVILEGE APPERTAINING THERETO, LOCATED IN THE VICTORY PARK ADDITION, TO THE TOWN OF BUNKIE, AVOYELLES PARISH LOUISIANA. BEING ALL OF LOT 7 AND THE EASTERN 20 FEET OF LOT 8, BLOCK 7 OF SAID ADDITION, WHICH ADDISON IS SHOWN ON PLAT RECORDED IN PLAT BOOK 5, PAGE 35 OF THE RECORDS OF AVOYELLES PARISH, LOUISIANA, THE AFORESAID MENTIONED PARCEL OF LAND HAVING A FRONTAGE OF 70 FEET ON GENERAL PATTON STREET AND R

And after:

LegalDesc254:

A CERTAIN PARCEL OR LOT OF GROUND WI BUILDINGS AND IMPROVEMENTS EREON, AND ALL RIGHTS, WAYS, AND PRIVILEGE APPERTAINING ERETO, LOCATED IN E VICTORY PARK ADDITION, TO E TOWN OF BUNKIE, AVOYELLES PARISH LOUISIANA. BEING ALL OF LOT 7 AND E EASTERN 20 FEET OF LOT 8, BLOCK 7 OF SAID ADDITION, WHICH ADDISON IS SHOWN ON PLAT RECORDED IN PLAT BOOK 5, PAGE 35 OF E RECORDS OF AVOYELLES PARISH, LOUISIANA, E AFORESAID MENTIONED PARCEL OF LAND HAVING A FRONTAGE OF 70 FEET ON GENERAL PATTON STREET AND RUNS BACK BETWEEN

Weird, huh? Is there any way just to get rid of every other Ascii code that isn't between 32 and 127, without making a REPLACE statement for every other code? I'm guessing that might solve the problem.

 

[RuralGuy]

Here's a couple of functions that you should put in a standard module named basStrings. Call FilterString(YourField) instead of Replace and it will strip everything but printable ASCII from the field. Have fun!

Private Function FilterString(strIn As String) As String
Dim Marker As Long
Dim OutString As String
If Len(strIn & "") > 0 Then
   OutString = strIn
   For Marker = 1 To Len(strIn)
      Mid(OutString, Marker, 1) = FilterIt(Mid(strIn, Marker, 1))
   Next Marker
   FilterString = OutString
Else
   '-- Do not attempt any conversion
   MsgBox "Invalid entry for 'FilterString' filter", vbExclamation + vbOKOnly
End If
End Function

Private Function FilterIt(InChr As String) As String
'-- Strip all but printable ascii 32-126
If Len(InChr) = 1 Then
   If Asc(InChr) > 31 And Asc(InChr) < 127 Then
      FilterIt = InChr
   Else
      FilterIt = ""
   End If
Else
   '-- Do not attempt any conversion
   MsgBox "Invalid entry for 'FilterIt' filter", vbExclamation + vbOKOnly
End If
End Function

 

[jnixon]

RG,
I did what you suggested. I created a module named basStrings, and copy/pasted the code you posted into the module. However, when I try to find the function in the query builder dropdowns, I can see the basStrings module, but neither of the functions display as usable arguments.

I tried typing it in anyway...

Legal: FilterString(dbo_SegmentLegal!LegalDescr)

I get this message when I try to run the field:

"Undefined function 'FilterString' in expression."

Did I do something wrong?

 

[RuralGuy]

Fooie! Change them both to Public functions, sorry.

 

[jnixon]

OH! Okay, yeah there we go. That did it! It looks like it did take everything out that I needed, and displays the whole memo field. Thanks!!

I don't mean to be too much of a pain.... but, I'm trying to export this to a delimited text file, and the memo field gets truncated. Any clues?

 

[RuralGuy]

Demited with what? You may have to toss the commas too. Can you change the delimiter to a tab maybe?

 

[jnixon]

Yeah, I've actually tried comma-delimited and tab-delimited. Both of them truncate the field, and it doesn't seem to matter if there's a " text qualifier either.

 

[RuralGuy]

I'm afraid I have not used any export yet and have little knowledge in that area. Maybe some kind sole will drop by and take pity on us and help. Maybe start another thread with "exported memo field truncating". How big is the field that is truncating? Which export command do you use?

 

[jnixon]

Thanks for all your help, RG! I will start a new thread.

 

[RuralGuy]

Glad to help. I'll watch the next thread for a resolution.