Results 1 to 5 of 5

Thread: How to setup an ADO Filter when data has embedded hyphen?

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Aug 2013
    Posts
    71

    How to setup an ADO Filter when data has embedded hyphen?

    Hi,
    I'm trying to setup an ADO Filter for multiple
    parameters but one of my data parameters
    has an embedded hyphen - e.g. May'17.
    How can I set up a filter for this condition?
    Is SQL my best alternative?

    Thanks for any help provided.

  2. #2
    Fanatic Member Spooman's Avatar
    Join Date
    Mar 2017
    Posts
    868

    Re: How to setup an ADO Filter when data has embedded hyphen?

    Ee

    Do you mean the apostrophe in May'17?

    If so, what is the nature of your problem?
    If so, how would you set the filter if there was no apostrophe?

    Spoo
    Last edited by Spooman; Jun 23rd, 2017 at 07:48 AM.

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Aug 2013
    Posts
    71

    Re: How to setup an ADO Filter when data has embedded hyphen?

    for a single parameter filter, one could use a 'Find':

    Code:
                                 obfRecords.MoveFirst
                                 obfRecords.Find "[Supplier_ID]= '" & supplier.Value & "'"
                                 If obfRecords.BOF And obfRecords.EOF Then
                                          (not in list)
                                 Else
                                          (in list)
                                 End If
    using a filter:

    Code:
                                 obfRecords.MoveFirst
                                 obfRecords.Filter "[Supplier_ID]= " & supplier.Value
                                 If obfRecords.BOF And obfRecords.EOF Then
                                          (not in list)
                                 Else
                                          (in list)
                                 End If

    'Find' doesn't provide allowance for multiple parameters while 'Filter' (apparently) does
    however, the 'Filter' doesn't seem to work when I try something like:

    Code:
                                 obfRecords.MoveFirst
                                 obfRecords.Filter "[Supplier_ID]= " & supplier.Value & " AND Product_ID= " & nextSkuRow.Cells(, 3).Value
                                 If obfRecords.BOF And obfRecords.EOF Then
                                          (not in list)
                                 Else
                                          (in list)
                                 End If

  4. #4
    Fanatic Member Spooman's Avatar
    Join Date
    Mar 2017
    Posts
    868

    Re: How to setup an ADO Filter when data has embedded hyphen?

    Ee

    Let me see if I understand your 3 cases

    Code:
    obfRecords.Find "[Supplier_ID]= '" & supplier.Value & "'"
    obfRecords.Filter "[Supplier_ID]= " & supplier.Value
    obfRecords.Filter "[Supplier_ID]= " & supplier.Value & " AND Product_ID= " & nextSkuRow.Cells(, 3).Value
    Let's assume ..

    supplier.Value is a string such as H2125
    nextSkuRow.Cells(, 3).Value is an integer such as 22

    Then, the string being created for Find or Filter would be as follows:

    case 1. obfRecords.Find .. [Supplier_ID]= 'H2125'
    case 2. obfRecords.Filter .. [Supplier_ID]= H2125
    case 3. obfRecords.Filter .. [Supplier_ID]= H2125 AND Product_ID= 22

    Does that match your expectations?

    Two other thoughts:

    1. Looks like a parameter is missing here, before the comma: nextSkuRow.Cells(, 3).Value
    2. Looks like you are missing the brackets here: AND Product_ID= 22

    Spoo
    Last edited by Spooman; Jun 24th, 2017 at 08:40 AM.

  5. #5
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: How to setup an ADO Filter when data has embedded hyphen?

    @spoo

    to handle quotes I made this Function and this take care of them...

    Code:
     Public Function strStr(ByVal sString As String) As String
            Dim s As String
            If Len(sString) = 0 Then
                strStr = "NULL"
                Exit Function
            End If
            'entfernt doppelte Hochkommata ''
            s = sString
            Do While InStr(1, s, Chr(39) & Chr(39)) > 0
                s = Replace(s, Chr(39) & Chr(39), Chr(39))
            Loop
            'Hochkommata doppeln
            s = Replace(s, Chr(39), Chr(39) & Chr(39))
            'entfernt doppelte DoppelHochkommata "
            Do While InStr(1, s, Chr(34) & Chr(34)) > 0
                s = Replace(s, Chr(34) & Chr(34), Chr(34))
            Loop
            'Hochkommata doppeln
            s = Replace(s, Chr(34), Chr(34) & Chr(34))
            'Zeichenkette in Hochkommata einschliessen
            strStr = Chr(39) & s & Chr(39)
        End Function
    in the SQL you would use it like this...

    Code:
      sSQL = "INSERT INTO "
                    sSQL = sSQL & "tbl_Adressen ("
                    sSQL = sSQL & "AN_Vorname, "
                    sSQL = sSQL & "AN_Nachname, "
                    sSQL = sSQL & "AN_Angelegt) "
                    sSQL = sSQL & " VALUES ("
                    sSQL = sSQL & "" & strStr(.AN_Vorname) & ", "
                    sSQL = sSQL & "" & strStr(.AN_Nachname) & ", "
                    sSQL = sSQL & "" & strStr(.An_Angelegt) & ")"
    regards
    Chris

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width