Search for matching data

hardy1976

Still learning...
Local time
Yesterday, 21:20
Joined
Apr 27, 2006
Messages
200
Hi,

Is this possible....

Have a table that holds conditions I want to find in a table for example
ID - SearchFor
1 - ;
2 - ,
3 - ALERT
This list can grow

I want to look through a table and output the ID and field it is found in into another table.

Can I do this if so how - help please
 
Thanks - not sure how that will work.

Dlookup whilst looping through the searchfor recordset?
 
I have loaded a file into Access, I need to vet the data to ensure there are no rouge characters in the data prior to exporting the data for load elsewhere.

I don't want to code the characters that I check for in the data (thinking future proof). I'd rather setup a table so that any characters specified will be searched for.

Make sense - sorry if thats not clear :(
 
So for each record in SearchFor table search in TableX if the value is present ?
Or in your own words : Dlookup whilst looping through the searchfor recordset

It will be the fastest way. Or make one big condition (with a lot of 'or') and add that to the Dlookup.
 
This is a function that will strip any character in the list contained with in it. I did not write it but found it, either here on one of the other access forums.
Code:
Public Function fStripIllegal(strCheck As String, Optional strReplaceWith As String = "") As String

    On Error GoTo StripIllErr
    'illegal file name characters included in default string are    ? [ ] /  = + < > :; * " , '

    Dim intI As Integer
    Dim intPassedString As Integer
    Dim intCheckString As Integer
    Dim strChar As String
    Dim strIllegalChars As String
    Dim intReplaceLen As Integer

    If IsNull(strCheck) Then Exit Function

    strIllegalChars = "?[]/=+<>:;,*" & Chr(34) & Chr(39) & Chr(13) & Chr(10)  'add/remove characters you need removed to this string

    intPassedString = Len(strCheck)
    intCheckString = Len(strIllegalChars)

    intReplaceLen = Len(strReplaceWith)

    If intReplaceLen > 0 Then   'a character has been entered to use as the replacement character
    
        If intReplaceLen = 1 Then   'check the character itself isn't an illegal character
        
            If InStr(strIllegalChars, strReplaceWith) > 0 Then
                MsgBox "You can't replace an illegal character with another illegal character", _
                       vbOKOnly + vbExclamation, "Invalid Character"
                fStripIllegal = strCheck
                Exit Function
            End If

        Else   'only one replacement character allowed

            MsgBox "Only one character is allowed as a replacement character", _
                   vbOKOnly + vbExclamation, "Invalid Replacement String"
            fStripIllegal = strCheck
            Exit Function
            
        End If
    End If

    If intPassedString < intCheckString Then

        For intI = 1 To intCheckString
            strChar = Mid(strIllegalChars, intI, 1)
            If InStr(strCheck, strChar) > 0 Then
                strCheck = Replace(strCheck, strChar, strReplaceWith)
            End If
        Next intI

    Else

        For intI = 1 To intPassedString
            strChar = Mid(strIllegalChars, intI, 1)
            If InStr(strCheck, strChar) > 0 Then
                strCheck = Replace(strCheck, strChar, strReplaceWith)
            End If
        Next intI

    End If

    fStripIllegal = Trim(strCheck)

StripIllErrExit:
    Exit Function

StripIllErr:
    MsgBox "The following error occured: " & Err.Number & vbCrLf _
         & Err.Description, vbOKOnly + vbExclamation, "Unexpected Error"

    fStripIllegal = strCheck

    Resume StripIllErrExit

End Function
 
But that function as a static list of illegal characters. The OP has a table who contains all the characters.
 
It would be quite simple to adapt it to use a table... And it will be quite difficult to specify things like CrLF in a table (although not impossible with some flags in the table to indicate it is a Chr String)
 
hardy1976,
Can you be more specific?
Are you looking for any of these words/strings/characters in any field of a table?
What if the character or string is found in multiple records?
Perhaps you could provide a few examples to clarify things.
And show us some sample records from the "other table".
It doesn't appear that anyone has guessed exactly what you want.
 

Users who are viewing this thread

Back
Top Bottom