Results 1 to 6 of 6
  1. #1
    Datamulcher is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2016
    Location
    Victoria, Canada
    Posts
    26

    Query for contains THESE characters but does not contain THOSE characters

    I'm working in a massive database and the problem we are experiencing is that there are 'non-characters' within specific fields.
    These values with 'non-characters' cause the required update macros to crash, and are fiendishly difficult to find. Major problem.

    Basically, it is a set of latitude and longitude records such as this:
    Loc1LatLong
    53.9505839, -113.1088853
    53.485211, -113.477789
    53.5957845, -113.5708019

    But when the senior analyst runs his macros he always finds they crash due to incorrect characters found in the above fields.
    He has shown me how he can copy a perfectly good looking value from the database and paste it into Notepad++ and it will display hidden / whitespace / non-characters in the value. [eg. "enter"/"hard stop" etc]
    These are likely a result of the coders copy and pasting from google etc.
    SO - the devilsh dilemma is:

    How do I identify all records that DO NOT HAVE: ONLY 0-9 and "," and "." (Numbers, commas, periods all OK. ANYTHING else is not OK.)?

    I'll be online if anyone needs clarification. Thanks!

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here is a quick function that may help.

    Code:
    '---------------------------------------------------------------------------------------
    ' Procedure : numcommadot
    ' Author    : mellon
    ' Date      : 27/04/2016
    ' Purpose   : Process an incoming string, remove any characters that are not in this set.
    '  "0123456789-,.". Return only the characters that match those in the set, ignore others.
    '---------------------------------------------------------------------------------------
    '
    Function numcommadot(sIn As String) As Double
        Dim c As String, i As Integer
    10    On Error GoTo numcommadot_Error
    20    sIn = Trim(sIn)  'remove leading/trailing spaces
        Dim sHold As String
    30    For i = 1 To Len(sIn)
    40      c = Mid(sIn, i, 1) 'check each character
    50      If InStr("0123456789-,.", c) <> 0 Then  'if acceptable keep it
    60          sHold = sHold & c
    70      Else                                '   if unacceptale, skip the character
    80      End If
    90    Next
    100   numcommadot = CDbl(sHold)  'return a decimal number
    110   On Error GoTo 0
    120   Exit Function
    
    numcommadot_Error:
    
    130   MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure numcommadot of Module AWF_Related"
    End Function
    A test routine
    Code:
    Sub testnumcommadot()
        Dim x(3) As String, i As Integer
        x(0) = "23.4333"
        x(1) = "-45.9" & vbCrLf & "445"
        x(2) = " -113.1088853"
        x(3) = "111.23-"
        For i = LBound(x) To UBound(x)
            Debug.Print x(i) & "   results as " & numcommadot(x(i))
        Next i
    End Sub
    Result:

    Code:
    23.4333   results as 23.4333
    -45.9
    445   results as -45.9445
    -113.1088853   results as -113.1088853
    111.23-   results as -111.23

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @orange,
    It looks to me that the dB field is a text field that has both Lat and Long.
    I modified your code to return a string.

    Code:
    '---------------------------------------------------------------------------------------
    ' Procedure : numcommadot2
    ' Author    : mellon
    ' Date      : 27/04/2016
    ' Purpose   : Process an incoming string, remove any characters that are not in this set.
    '  "0123456789-,.". Return only the characters that match those in the set, ignore others.
    '---------------------------------------------------------------------------------------
    '
    Function numcommadot2(sIn As String) As String
        Dim c As String, i As Integer
        On Error GoTo numcommadot_Error
        sIn = Trim(sIn)  'remove leading/trailing spaces
        Dim sHold As String
        For i = 1 To Len(sIn)
            c = Mid(sIn, i, 1)    'check each character
            If InStr("0123456789-,.", c) <> 0 Then  'if acceptable keep it
                sHold = sHold & c
            Else                                '   if unacceptale, skip the character
            End If
        Next
        
        On Error GoTo 0
        numcommadot2 = sHold
        Exit Function
    
    numcommadot_Error:
    
        MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure numcommadot of Module AWF_Related"
    End Function
    Usage for TEXT Field:
    Code:
    Public Sub DelNonPrintable()
    
        Dim r As DAO.Recordset
        Dim sSQL As String
        Dim sTmp As String
    
        sSQL = "SELECT Loc1LatLong FROM LatLng1"  '<<-- replace the red text with your field name and table name
    
        Set r = CurrentDb.OpenRecordset(sSQL)
    
        If Not r.BOF And Not r.EOF Then
            r.MoveLast
            r.MoveFirst
    
            Do While Not r.EOF
                sTmp = vbNullString
                sTmp = r("Loc1LatLong") & ""
                sTmp = numcommadot2(sTmp)
    
                r.Edit
                r("Loc1LatLong") = sTmp
                r.Update
    
               '       Debug.Print sTmp
    
                r.MoveNext
            Loop
    
        End If
    
        r.Close
        Set r = Nothing
    
    End Sub

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Good point Steve. I was dealing separately with lat and lon.
    Agree it should do the entire string with format XXX.xxx,-WWW.www

    Here is revised function and test to deal with 1 complete string and return a string.
    2 changes involved

    Code:
    '---------------------------------------------------------------------------------------
    ' Procedure : numcommadot
    ' Author    : mellon
    ' Date      : 27/04/2016
    ' Purpose   : Process an incoming string, remove any characters that are not in this set.
    '  "0123456789-,.". Return a string with only the characters that match those in the set, ignore others.
    '---------------------------------------------------------------------------------------
    '
    Function numcommadot(sIn As String) As String 'String in, string out
        Dim c As String, i As Integer
    10    On Error GoTo numcommadot_Error
    20    sIn = Trim(sIn)  'remove leading/trailing spaces
        Dim sHold As String
    30    For i = 1 To Len(sIn)
    40      c = Mid(sIn, i, 1) 'check each character
    50      If InStr("0123456789-,.", c) <> 0 Then  'if acceptable keep it
    60          sHold = sHold & c
    70      Else                                '   if unacceptale, skip the character
    80      End If
    90    Next
    100   numcommadot = sHold  'return a string NOT Double
    110   On Error GoTo 0
    120   Exit Function
    
    numcommadot_Error:
    
    130   MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure numcommadot of Module AWF_Related"
    End Function
    Revised test routine

    Code:
    Sub testnumcommadot()
        Dim x(3) As String, i As Integer
        x(0) = " 23.4333,-133.549"
        x(1) = "-45.9" & vbCrLf & "445,56.8888"
        x(2) = "45.2466,-113.1088853"
        x(3) = "111.23-"
        For i = LBound(x) To UBound(x)
            Debug.Print x(i) & "   results as " & numcommadot(x(i))
        Next i
    End Sub
    test results:

    Code:
    23.4333,-133.549   results as 23.4333,-133.549
    -45.9
    445,56.8888   results as -45.9445,56.8888
    45.2466,-113.1088853   results as 45.2466,-113.1088853
    111.23-   results as 111.23-

  5. #5
    Datamulcher is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2016
    Location
    Victoria, Canada
    Posts
    26
    Sorry I haven't checked in sooner. Great work guys!! Thanks for doing this.

    One problem. My bad in not communicating the issue correctly. - What I am looking for is VERY close to this, but almost the inverse.
    The exact objective is to identify which records contain malformed lat/longs (using the same criteria as has been applied above) and output which records, in this case the PersonID associated with the lat/long, contain these bad lat/longs. Maybe something that simply outputs the lat/longs with bad chars would do the trick, then I could run a query looking for these bad lat/longs which had been identified.

    We (both me and the senior data analyst) cannot find a way to identify the bad records, so these may either a) be fixed or b) be excluded from the daily macro run (which is crashing half the time due to these null characters or other chars being in the field, which is a Text field.)
    Everyone here has been very helpful so far! Great jumping off points, but honestly for me, my VBA skills are not good enough to figure a way to apply the above solutions to the problem.

    Thanks again, and thanks in advance if you take the challenge to figure out a way to identify the records!

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Easy to modify the function.

    Now the question is HOW do yo want to identify the bad fields?
    Is there a pK field to be able to find the bad record?
    Are you using a form?
    Do you have a text field in the record that could be set to "Bad"?
    Write to the Immediate window?
    Write the PK field and the bad Lat/Long field to a text file?





    Edit: added a example dB
    Attached Files Attached Files

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 11-25-2015, 04:41 AM
  2. Match query using 5 characters
    By Luvyourbag in forum Queries
    Replies: 2
    Last Post: 03-12-2015, 07:29 PM
  3. Replies: 9
    Last Post: 02-11-2013, 03:09 PM
  4. Special characters in Default Value and Query
    By blacksaibot in forum Queries
    Replies: 1
    Last Post: 03-07-2012, 10:36 AM
  5. Union query only returning 255 characters
    By jpkeller55 in forum Queries
    Replies: 25
    Last Post: 10-05-2010, 05:51 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums