Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Posts
    4

    Unanswered: Create SQL statement from array

    I am trying to add a 'keyword style' search engine for my database.

    Can anyone tell me how to convert a string like

    "blue box round"

    into an SQL statement like

    WHERE (([Desc] = "blue") OR ([Desc] = "box") OR ([Desc] = "round"))

    I assume I should use the Split() function, but I do not understand how to use the array it creates...

    I can't even find a way of viewing the data within the array to check it..

    Can anyone help me with the code, or explain how to work with arrays..

    Thanks

    Nick

  2. #2
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Code:
    "SELECT * FROM MyTable WHERE (([DESC] in (""" & Replace("blue box round", " ", """,""") & """)))"

  3. #3
    Join Date
    Mar 2003
    Posts
    11

    Re: Create SQL statement from array

    Sorry, I wasn't thinking..

    I meant:

    "blue box round"

    into an SQL statement like

    WHERE (([Desc] = "*" & "blue" & "*") OR ([Desc] = "*" & "box" & "*") OR ([Desc] = "*" & "round" & "*"))

    Basically, I want a list of all records where one or more of the 'keywords' are found within the [Desc] field...

    The [Desc] field would contain data like:

    "Small square box with yellow stripes and a red lid"

    I want this to appear in the results because it contains the word "box"..

    Thanks

    Nick

  4. #4
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179

    Re: Create SQL statement from array

    Aight...

    If you insist on using split/join, try this.
    Code:
    "SELECT * FROM MyTable WHERE (([Desc] = ""*"" & """ & _
    Join(Split("Small square box with yellow stripes and a red lid", " "), """ & ""*"") OR ([Desc] = ""*"" & """) & _
    """ & ""*""))"
    The split function takes a long string and separates it, based on the deliminator, into array elements. In this case, the split function will make a single-dimension array with 10 elements. To see it, try this.
    Code:
       Dim intArrayIndex As Integer
       Dim strMyCriteria() As String
       strMyCriteria = Split("Small square box with yellow stripes and a red lid", " ")
       For intArrayIndex = LBound(strMyCriteria) To UBound(strMyCriteria)
          Debug.Print strMyCriteria(intArrayIndex)
       Next
    The join function takes the array, and concatenates each element with a deliminator, to make one long string. In this case, we separated it with
    Code:
    " & "*") OR ([Desc] = "*" & "
    Alternatively, you could still use the replace function.
    Code:
    "SELECT * FROM MyTable WHERE (([DESC] = ""*"" & """ & _
    Replace("Small square box with yellow stripes and a red lid", " ", """ & ""*"") OR ([Desc] = ""*"" & """) & _
    """ & ""*""))"
    Either way, you end up with the SQL statement you're looking for.

Posting Permissions

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