How do I destroy those wierd ASCII symbols? 3

[Guest_imported]

When I try to import records from a FileMaker Pro Database into an Access database, many of the fields have strange hollow squares that take the place of line returns.

How do I get rid of that from within the access database? I can't seem to cut the symbols, so a simple find-and-replace doesn't work.

Thank You,
Randall

 

[ghubbell]

Hi Randall,
You can "plow" all your data through this function:

Function CRLFRemover(stringIN As String) As String
On Error GoTo Err1
Dim intRemain As Integer, intPosition As Integer, intOrigLength As Integer
CRLFRemover = stringIN
intOrigLength = Len(stringIN)
intRemain = intOrigLength
intPosition = 1
Do Until intRemain = 0
intPosition = InStr(intPosition, CRLFRemover, vbCrLf)
If intPosition <> 0 Then
CRLFRemover = Left(CRLFRemover, intPosition - 1) & &quot; &quot; & Mid(CRLFRemover, intPosition + 2)
Else
GoTo Exit1
End If
Loop

Exit1:
Exit Function

Err1:
MsgBox Err.Number & &quot; &quot; & Err.Description, vbInformation, &quot;''CRLF'' remover error.&quot;
Resume Exit1
End Function

This will strip carriage returns and line feeds from a string and return a nice straight string in its place. If you need some help on how to implement it just write back! ;-) Gord
ghubbell@total.net

 

[tombirch]

Try this one. It will return only the normal ascii caracters (ascii number 32 to 126 and 160 to 255.

Function adjust(var) as string
Dim y As Integer, xst As String
dim nystr As String, i As Integer
if isnull(var) then
exit function
For i = 1 To Len(var)
xst = Mid(var, i, 1)
For y = 32 To 126
If StrComp(xst, Chr, vbBinaryCompare) = 0 Then
nystr = nystr & xst
End If
Next y
For y = 160 To 255
If StrComp(xst, Chr, vbBinaryCompare) = 0 Then
nystr = nystr & xst
End If
Next y
Next i
adjust = nystr
End Function

Good luck with it.

Tom -------------------
Freetime is for training.

 

[MichaelRed]

N guarntees, but this is a bit shorter and should work for both '97 and 2K

Public Function basAltrmvCrLf(LineIn As String) As String

    'Usage:
    'ThisHereLine$ = "The Quick Brown Fox" & vbcr & "Jumped Over" & VbLf & "The lazy" & vbcrlf & "Brown Dog"
    'Print basAltrmvCrLf(ThisHereLine$)
    'The Quick Brown Fox Jumped OverThe lazy Brown Dog

    Dim Idx As Integer
    Dim RemLine As String

    RemLine = LineIn
    Idx = 1

    Idx = InStr(RemLine, vbCr)
    Do While Idx <> 0
        If (Idx <> 0) Then
            LineNew = LineNew & left(RemLine, Idx - 1) & &quot; &quot;
            RemLine = right(RemLine, Len(RemLine) - Idx)
        End If
        Idx = InStr(RemLine, vbCr)
    Loop

    LineNew = LineNew & RemLine
    RemLine = LineNew
    LineNew = &quot;&quot;

    Idx = InStr(RemLine, vbLf)
    Do While Idx <> 0
        If (Idx <> 0) Then
            LineNew = LineNew & left(RemLine, Idx - 1) & &quot; &quot;
            RemLine = right(RemLine, Len(RemLine) - Idx)
        End If
        Idx = InStr(RemLine, vbLf)
    Loop

    basAltrmvCrLf = LineNew & RemLine
End Function

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[Hobbster]

Gord (or anyone else),

How do you implement the programming examples that you mentioned into the database?


Eric

 

[MichaelRed]

the functions all accept a string as input (these would be your strings w/ &quot;Weird&quot; characters) and return &quot;Non-Weird&quot; strings. Pick your 'poision' and do an update of the field(s) with the selectd function.

In the query by grid the update to would look like:

functionname([FieldName])
MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[ghubbell]

Hi Eric,
You might want to use something like this right after you've imported a bunch of records as Randall has, in which case you would build a recordset based off of the new data table, place the data from one record in to the function, then return the results back in place of the original. Then keep looping through the recordset until you're finished:

Dim Rs as Recordset
Dim Db as Database
Dim SQL as string
Set Db = CurrentDb()
SQL = &quot;Select mytable.* from mytable&quot;
Set Rs= Db.openrecordset(SQL,dbopendynaset)
Do Until Rs.EOF
Rs.Edit
Rs![problemfield] = CRLFRemover(Rs![problemfield])
Rs.Update
Rs.movenext
Loop

(Above is typed in right here and I've left out a few little formalities like checking if there is anything in the Rs and error handling but you should get the idea...)

Perhaps you need this &quot;live&quot; on a form? You could call the function from any event say, After Update: after someone updates a text or memo field and you want to make sure they didn't put any line feeds in:

Me![Nameofyourfield] = CRLFRemover(Me![NameOfYourField])

Oh! As I just see Michael's reply arrive here, that way too! Gord
ghubbell@total.net