Replacing strange text

[igillesp]

Hi all

I'm pulling text out of a LIMS Database via ODBC and the bit I want is surrounded by strange characters which I'd like to remove (example attached).

Any ideas how to do it in VBA or what the character code is?

Thanks,

Iain

 

[DCrake]

If you are simply linking to the lims data in Access then doing any sort of replace on the contents may have disasterous effects on the parent application. If what you have is COPY of it then there are ways to do this. Which senerio have you got?

 

[igillesp]

The latter.

I'm using a make-table query to write the results to a table called HUS.

I've writtent the code below to concatenate the multiple entries, but would like to incorporate a way of stripping the extra characters from the HUS.FILLV1 field.

Iain

Public Sub DiagCode()
DoCmd.SetWarnings False
'Create Diag table with a single record for each MolisID
DoCmd.RunSQL ("SELECT HUS.MolisID, '' AS DIAG INTO Diag FROM HUS GROUP BY HUS.MolisID, '' ORDER BY HUS.MolisID;")
'Create a recordset of Diag table for population
Dim rsD As DAO.Recordset
Set rsD = CurrentDb.OpenRecordset("SELECT * FROM Diag")
rsD.MoveFirst
Do While Not rsD.EOF
    'Create a sub-recordset of all records in HUS with the same MolisID
 
    Dim rsSS As DAO.Recordset 'SS=subset
    Dim ssSQL As String
        ssSQL = "SELECT HUS.MolisID, HUS.DIAGCODE, HUS.FILLV1 " & _
                "FROM HUS " & _
                "WHERE HUS.MolisID='" & rsD!MolisID & "' " & _
                "ORDER BY HUS.DIAGCODE, HUS.FILLV1;"
 
    Set rsSS = CurrentDb.OpenRecordset(ssSQL)
 
     'Work through the records concatenating the results in DiagString
     Dim DiagString As String
 
     rsSS.MoveFirst
 
     Do While Not rsSS.EOF
        DiagString = DiagString & " " & Trim(rsSS!DiagCode & " " & rsSS!FILLV1) & " "
     rsSS.MoveNext
     Loop
With rsD
    .Edit
    !DIAG = Trim(DiagString)
    .Update
End With
DiagString = ""
rsD.MoveNext
Loop
 
DoCmd.SetWarnings True
End Sub

 

[Rx_]

It has been years, but my LIMS conversion required a ODBC driver via the ADO driver, could not get it to work with the standard ODBC. Some of the private labs had Access or SQL Server, they were more straight forward to work with.

Looks like your progress is on the right path, but wondered if you could provide more details about the LIMS data store?

For others interested in LIMS conversion, this document provides some general information. Data conversion starts on about page 7.
http://www.assaynet.com/articles/lims_course.pdf

 

[jdraw]

The boxes indicate that those are non-displayable / unprintable characters. They are very likely control characters (CR LF TAB FF etc).
You could run a query to identify one of the characters.

Something like this could work to identify the "bad" character at the start of FILLV1. This will give you some identifying info from your records - MolisID, DIAGCODE and the ASCII code of the "bad" character in position 1 of FILLV1

SELECT HUS.MolisID, HUS.DIAGCODE, Asc(Left([HUS.FILLV1, 1)) AS BadCharNumber
FROM HUS
WHERE Left([HUS.FILLV1]) Not Like "[A-Z0-9]";

You can look up the ASCII characters and numbers here
http://www.cs.utk.edu/~pham/ascii.html

You could then write some code to remove the bad character.

 

[igillesp]

Thanks!

I think this had a type on it, but a corrected version...

SELECT HUS.MolisID, HUS.DIAGCODE, Asc(Left([HUS].[FILLV1],1)) AS BadCharNumber
FROM HUS
WHERE (((Asc(Left([HUS].[FILLV1],1))) Not Like "[A-Z0-9]"));

...returned character 28.

I adopted code from this page...

Public Sub DiagCode()
DoCmd.SetWarnings False
'Create Diag table with a single record for each MolisID
DoCmd.RunSQL ("SELECT HUS.MolisID, '' AS DIAG INTO Diag FROM HUS GROUP BY HUS.MolisID, '' ORDER BY HUS.MolisID;")
'Create a recordset of Diag table for population
Dim rsD As DAO.Recordset
Set rsD = CurrentDb.OpenRecordset("SELECT * FROM Diag")
rsD.MoveFirst
Do While Not rsD.EOF
    'Create a sub-recordset of all records in HUS with the current MolisID
 
    Dim rsSS As DAO.Recordset 'SS=subset
    Dim ssSQL As String
        ssSQL = "SELECT HUS.MolisID, HUS.DIAGCODE, HUS.FILLV1 " & _
                "FROM HUS " & _
                "WHERE HUS.MolisID='" & rsD!MolisID & "' " & _
                "ORDER BY HUS.DIAGCODE, HUS.FILLV1;"
 
    Set rsSS = CurrentDb.OpenRecordset(ssSQL)
 
     'Work through the records concatenating the results in DiagString
     Dim DiagString As String
 
     rsSS.MoveFirst
 
    Dim Comment As String
    Dim tmpStr As String
    Dim sChar As String
    Dim Counter As Integer
    Comment = rsSS!FILLV1 & ""
    If InStr(1, Comment, Chr(28)) <> 0 Then
        For Counter = 1 To Len(Comment)
            If Mid(Comment, Counter, 1) = Chr(28) Then
                sChar = ""
            Else
                sChar = Mid(Comment, Counter, 1)
            End If
            tmpStr = Trim(tmpStr)
            tmpStr = tmpStr & sChar
        Next Counter
            Comment = tmpStr
    Else
            Comment = tmpStr
    End If
 
     Do While Not rsSS.EOF
        DiagString = DiagString & " " & Trim(rsSS!DiagCode & " " & Comment) & " "
     rsSS.MoveNext
     Loop
With rsD
    .Edit
    !DIAG = Trim(DiagString)
    .Update
End With
DiagString = ""
rsD.MoveNext
Loop
 
DoCmd.SetWarnings True
End Sub

...but it doesn't appear to be stripping the characters.

Am I doing something wrong?

Thanks,

Iain

PS In response to the other post its a vision4health (ex SYSMEX) Molis LIMS system. Other than that I'm in the dark

 

[jdraw]

The code I showed was to get an idea of what the bad character was at the beginning of
FILLV1. The 28 is a FS file separator character. But that only show the one character in the leftmost position of FILLV1.
The thing is you could have other characters that are represented by the "boxes".

If you have 5 or 6 boxes, then they may all be 28's or they could be a combination.

In order to remove all of the bad characters, you'll have to find out what they are.

You could take FILLV1 and go thru it character by character and report anything that isn't "[A-Z0-9]"

Here is a function I got a few years ago from Access Web (Dev Ashish). It will only output certain characters, based on the conditions within the function.
In this default example it will output only alphabetics, but you could adjust it to output alphabetic and numeric A-Z a-z 0-9. That would remove all of the control and non printables. ASC 48 thru 57 are 0 -9. You may need to keep
+-/* etc. depending on your data.

You might want to do some testing with a few records to see if it will do what you need.

Function fExtractStr(ByVal strInString As String) As String
' From Dev Ashish
'(Q) How do I extract only characters from a string which has both numeric and alphanumeric characters?

'(A) Use the following function. Note that the If loop can be modified to extract either both Lower and Upper case character or either
'Lower or Upper case characters.

'************ Code Start **********

Dim lngLen As Long, strOut As String
Dim i As Long, strTmp As String

    lngLen = Len(strInString)
    strOut = ""
    For i = 1 To lngLen
        strTmp = Left$(strInString, 1)
        strInString = Right$(strInString, lngLen - i)
        'The next statement will extract BOTH Lower and Upper case chars
        If (Asc(strTmp) >= 65 And Asc(strTmp) <= 90) Or _
            (Asc(strTmp) >= 97 And Asc(strTmp) <= 122) Then
            'to extract just lower case, use the limit 97 - 122
            'to extract just upper case, use the limit 65 - 90
            strOut = strOut & strTmp
        End If
    Next i
    fExtractStr = strOut
End Function

 

[igillesp]

This worked a treat, although I modified it...

        If (Asc(strTmp) = 32) Or _
           (Asc(strTmp) >= 65 And Asc(strTmp) <= 90) Or _
           (Asc(strTmp) >= 97 And Asc(strTmp) <= 122) Then

...as it was stripping spaces between words.

Thanks!

Iain