none
VBA doesn't recognize data in text box separated by commas RRS feed

  • Question

  • I have a text box that we enter an application code into. I have set my form up so that it will compare this field to data in a table and then if it's in the table it comes back and presents it in another text box as being positive. The problem I'm having is sometimes we have multiple application codes in the box separated with commas. These don't turn up as positive.  I need it to let me know if one or more are in the table.

    What I've done:

    manually entered text box = txtAppCode. 

    Query = qryAppCode, searches tblAppCodes, fields AIT.   This query is filtered by [Forms]![fmServerInfo]![txtAppCode].

    I have a hidden text box, name = txtDlookupAppCode, Control = =DLookUp("[AppCode]","[qryAppCode]","[AIT]=[txtAppCode]")

    Then the warning box = txtAppCodeWarning

    txtAppCode has an After Update event =

    If Me.txtDlookupAppCode Like Me.txtAppCode Then
        Me.txtAppCodeWarning.Value = "App Code Present"
    Else
        Me.txtAppCodeWarning.Value = ""
    End If

    Wednesday, September 30, 2015 4:45 PM

Answers

  • If you have multiple codes in the textbox separated by commas, you will have to parse them into separate values yourself and look up each one in turn.

    There are a couple of ways to do the parsing:

    1. Scan character by character and build each item by concatenating a character at a time until you hit a comma.

    2. Search for the commas and extract the substrings between the commas (Use INSTR function).

    When doing this type of thing you will potentially have to handle such things as: a comma at the beginning with nothing before it, a comma at the end with nothing after it, two or more consecutive commas with nothing between them.



    Wednesday, September 30, 2015 7:23 PM
  • If you have multiple codes in the textbox separated by commas, you will have to parse them into separate values yourself and look up each one in turn.

    There are a couple of ways to do the parsing:

    Scan character by character and build each item a character at a time until you hit a comma.

    Search for the commas and extract the substrings between the commas (Use INSTR function).


    I'd recommend the VBA Split() function for this kind of parsing.  It will parse a delimited string into an array of values in a single call.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by Carl_S_S Thursday, October 1, 2015 2:49 PM
    Wednesday, September 30, 2015 7:30 PM
  • Here's a function you can use to see if a particular value is in a delimited list:

    Function fncIsInDelimitedList( _
                    TheValue As Variant, _
                    TheList As String, _
                    Optional Delim As String = ",", _
                    Optional PartialMatch As Boolean = False) _
                As Boolean
                
        ' Accepts a string that can be interpreted as a delimited list,
        ' and a value, with optional arguments specifying the delimiter
        ' and whether partial-match logic is in effect.
        ' Returns True if the value is found in the delimited list,
        ' False if not.
        '
        ' Arguments:
        '
        '   TheValue -
        '       This is the value to be looked for in the list.
        '       Non-text values will be converted to text for comparison.
        '
        '   TheList -
        '       This is a string containing a delimited list of values to
        '       be searched for the value.
        '
        '   Delim -  (Optional)
        '       This is a string specifying the delimiter used in the list.
        '       Default value is a comma (",").
        '
        '   PartialMatch -  (Optional)
        '       This is a string boolean value specifying whether partial matches
        '       should be accepted.  If PartialMatch = True, then the function
        '       will return True if any of the list items is contained in TheValue.
        '       If PartialMatch = False, then the function will return True only
        '       if one of the list items exactly matches TheValue.
    
        ' Copyright © Dirk Goldgar, 2012
        ' Permission: You may use this function in your applications and
        '       distribute it freely, provided that the copyright notice
        '       remains unchanged.
    
        Static strOriginalList As String
        Static strList As String
        Static astrListValues() As String
        Static strOriginalDelim As String
        Static strDelim As String
        
        Dim strValue As String
        Dim strDelimPadRight As String
        Dim strDelimPadLeft As String
        Dim I As Long
        
        strValue = Trim$(TheValue & vbNullString)
    
        If Len(strValue) = 0 Then
            Exit Function
        End If
    
        ' If the list (or delimiter) has changed, parse to set up a new list.
        If (TheList <> strOriginalList) Or (Delim <> strOriginalDelim) Then
        
            ' Make working copies of the list and the delimiter.
            strOriginalList = TheList
            strList = TheList
            strOriginalDelim = Delim
            strDelim = Delim
            
            astrListValues = Split(strOriginalList, strDelim)
            
            ' Remove any spaces that may surround the delimited values.
            For I = LBound(astrListValues) To UBound(astrListValues)
                astrListValues(I) = Trim$(astrListValues(I))
            Next I
        
        End If
    
        ' Okay, now we have a list of values parsed into the array, astrListValues.
        ' Is the value we were passed in the list.
        
        For I = LBound(astrListValues) To UBound(astrListValues)
            If PartialMatch Then
                If TheValue Like "*" & astrListValues(I) & "*" Then
                    fncIsInDelimitedList = True
                    Exit Function
                End If
            End If
            If TheValue = astrListValues(I) Then
                fncIsInDelimitedList = True
                Exit Function
            End If
        Next I
        
    Exit_Point:
        Exit Function
    
    End Function
    

    Using that function, your AfterUpdate event procedure could look something like this:

    If IsNull(DLookup("AppCode","tblAppCodes","fncIsInDelimitedList([AppCode], [Forms]![fmServerInfo]![txtAppCode])")) Then
        ' None of the app codes in the list was found.
        Me.txtAppCodeWarning.Value = ""
    Else
        ' We found at least one of the app codes from the list.
        Me.txtAppCodeWarning.Value = "App Code Present"
    End If
    

    You wouldn't need the query or the hidden control.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Proposed as answer by André Santo Wednesday, September 30, 2015 7:50 PM
    • Marked as answer by Carl_S_S Thursday, October 1, 2015 2:48 PM
    Wednesday, September 30, 2015 7:48 PM

All replies

  • If you have multiple codes in the textbox separated by commas, you will have to parse them into separate values yourself and look up each one in turn.

    There are a couple of ways to do the parsing:

    1. Scan character by character and build each item by concatenating a character at a time until you hit a comma.

    2. Search for the commas and extract the substrings between the commas (Use INSTR function).

    When doing this type of thing you will potentially have to handle such things as: a comma at the beginning with nothing before it, a comma at the end with nothing after it, two or more consecutive commas with nothing between them.



    Wednesday, September 30, 2015 7:23 PM
  • If you have multiple codes in the textbox separated by commas, you will have to parse them into separate values yourself and look up each one in turn.

    There are a couple of ways to do the parsing:

    Scan character by character and build each item a character at a time until you hit a comma.

    Search for the commas and extract the substrings between the commas (Use INSTR function).


    I'd recommend the VBA Split() function for this kind of parsing.  It will parse a delimited string into an array of values in a single call.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by Carl_S_S Thursday, October 1, 2015 2:49 PM
    Wednesday, September 30, 2015 7:30 PM
  • Here's a function you can use to see if a particular value is in a delimited list:

    Function fncIsInDelimitedList( _
                    TheValue As Variant, _
                    TheList As String, _
                    Optional Delim As String = ",", _
                    Optional PartialMatch As Boolean = False) _
                As Boolean
                
        ' Accepts a string that can be interpreted as a delimited list,
        ' and a value, with optional arguments specifying the delimiter
        ' and whether partial-match logic is in effect.
        ' Returns True if the value is found in the delimited list,
        ' False if not.
        '
        ' Arguments:
        '
        '   TheValue -
        '       This is the value to be looked for in the list.
        '       Non-text values will be converted to text for comparison.
        '
        '   TheList -
        '       This is a string containing a delimited list of values to
        '       be searched for the value.
        '
        '   Delim -  (Optional)
        '       This is a string specifying the delimiter used in the list.
        '       Default value is a comma (",").
        '
        '   PartialMatch -  (Optional)
        '       This is a string boolean value specifying whether partial matches
        '       should be accepted.  If PartialMatch = True, then the function
        '       will return True if any of the list items is contained in TheValue.
        '       If PartialMatch = False, then the function will return True only
        '       if one of the list items exactly matches TheValue.
    
        ' Copyright © Dirk Goldgar, 2012
        ' Permission: You may use this function in your applications and
        '       distribute it freely, provided that the copyright notice
        '       remains unchanged.
    
        Static strOriginalList As String
        Static strList As String
        Static astrListValues() As String
        Static strOriginalDelim As String
        Static strDelim As String
        
        Dim strValue As String
        Dim strDelimPadRight As String
        Dim strDelimPadLeft As String
        Dim I As Long
        
        strValue = Trim$(TheValue & vbNullString)
    
        If Len(strValue) = 0 Then
            Exit Function
        End If
    
        ' If the list (or delimiter) has changed, parse to set up a new list.
        If (TheList <> strOriginalList) Or (Delim <> strOriginalDelim) Then
        
            ' Make working copies of the list and the delimiter.
            strOriginalList = TheList
            strList = TheList
            strOriginalDelim = Delim
            strDelim = Delim
            
            astrListValues = Split(strOriginalList, strDelim)
            
            ' Remove any spaces that may surround the delimited values.
            For I = LBound(astrListValues) To UBound(astrListValues)
                astrListValues(I) = Trim$(astrListValues(I))
            Next I
        
        End If
    
        ' Okay, now we have a list of values parsed into the array, astrListValues.
        ' Is the value we were passed in the list.
        
        For I = LBound(astrListValues) To UBound(astrListValues)
            If PartialMatch Then
                If TheValue Like "*" & astrListValues(I) & "*" Then
                    fncIsInDelimitedList = True
                    Exit Function
                End If
            End If
            If TheValue = astrListValues(I) Then
                fncIsInDelimitedList = True
                Exit Function
            End If
        Next I
        
    Exit_Point:
        Exit Function
    
    End Function
    

    Using that function, your AfterUpdate event procedure could look something like this:

    If IsNull(DLookup("AppCode","tblAppCodes","fncIsInDelimitedList([AppCode], [Forms]![fmServerInfo]![txtAppCode])")) Then
        ' None of the app codes in the list was found.
        Me.txtAppCodeWarning.Value = ""
    Else
        ' We found at least one of the app codes from the list.
        Me.txtAppCodeWarning.Value = "App Code Present"
    End If
    

    You wouldn't need the query or the hidden control.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Proposed as answer by André Santo Wednesday, September 30, 2015 7:50 PM
    • Marked as answer by Carl_S_S Thursday, October 1, 2015 2:48 PM
    Wednesday, September 30, 2015 7:48 PM
  • Thank you guys.  This looks like what I'm looking for.
    Thursday, October 1, 2015 2:48 PM