Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2011
    Inside your mind

    Question Unanswered: Dynamic SQL SELECT statements

    Hi guys,

    I can think of a few ways to achieve the desired results, but my question is more for opinions than a definitive answer.

    What I have at the moment is a table, which I wish to be able to search through, and retrieve records from, based around 4 fields, namely; [First Name], [Surname], [Company], [Purchase Date].

    At the moment I have a setup that looks like:

       First Name:   [XXXXXXX]  [>]    [^^^^^^^^^^^^^^^^^^^^^^^^^^^]
          Surname:   [XXXXXXX]  [>]    [                           ]
          Company:   [XXXXXXX]  [>]    [                           ]
    Purchase Date:   [XXXXXXX]  [>]    [___________________________]
    [XXX] = Text box
    [>] = Toggle box
    [^_] = List box

    Now, what I want to be able to do, is for someone to type in, for example, a Surname and a Purchase Date, push both the appropriate toggle boxes, and the listbox update to show any results that match a:

    WHERE [Surname] = 'Me.surnameInput.value'
    AND [Purchase Date] = 'Me.purchaseDateInput.value'
    The problem is, obviously, that these toggle boxes are dynamic, sometimes one might be active, other times all four might be, so the where statement would need to grow and shrink in relation to the toggle boxes.

    Now I know I could set up a _Click() event for each toggle box, with all the different situations, and their layouts, but that's quite a lot of work, and is an exponential problem if, at a later date, more search criteria are required.

    So, I'm basically wondering if anyone reading this can shed some light on how they'd approach it.

    I know I could have a simple submit button setup, but I'm trying to expand my knowledge of this language.

    Thanks in advance guys!
    Last edited by kez1304; 07-19-11 at 07:43.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    build a SQL statement based on the user input
    use that sql statement to populate the list box (or combo box if preferred) manually
    I don't think you need the 'toggle box', presume you mean check box. you can test if there is input in the text boxes and use those values

    strWhereClause = " WHERE 1=1"
    if strlen (txtFName) > 0 then strWhereClause = strWhereClause & " AND forename = '" & txtFname & "'"
    if isdate (txtpurchasedate) then strWhereClause = strWhereClause & " AND purchasedate = " & cdate(txtpurchasedate)

    the 1=1 means there's always a valid term int he where clause
    text literals must be encapsualted with either ' or " to delimit the text
    date literasl must be encapusalted with # and be in either ISO ("#yyyy/nn/dd#" or US format ("#mm/dd/yyyy#")
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2011
    Inside your mind
    Got it sorted eventually, never thought to use:

    WHERE 1 = 1

    Live and learn I guess.

    Thanks for the help buddy.

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