Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Location
    Richmond, VA
    Posts
    11

    Unanswered: Need some syntax help...PLEASE!

    I am creating a web page for my father. I am using DWMX2004 with the ASP with VBScript.

    I have 2 drop down menus. As it is right now the user must select something from each menu. I would like to add an "Any" option in the event that the user does not know one of the 2 options they are searching for.

    I have a menu item called Any, but I do not know what to use for its value.

    My search statement is as follows:

    SELECT Catagory, Description, Varietal, Wine, Winery
    FROM CCwines
    Where Catagory = 'MMColParam' AND Varietal = 'mnuVarietal'
    ORDER BY Wine ASC

    MMColParam is one of the drop down menus and the mnuVarietal is the other.

    What value should I give the Any option in my drop down list to make it pull from any of the entries in the field?

    I have tried *, '*', "*" and various others.

    Any help is GREATLY appreciated!

    Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    interesting that you should call it the "any" option, that's what i call it too



    The "any" option in dynamic search SQL
    (registration required, but it's free)


    rudy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2003
    Location
    Richmond, VA
    Posts
    11
    Thanks for the link r937. I will try that and see if it works.

    Thanks again!

  4. #4
    Join Date
    Oct 2003
    Location
    Richmond, VA
    Posts
    11
    I tried what that site instructed with no success.

    Any other thoughts?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    can we see the sql that gave no success?

  6. #6
    Join Date
    Oct 2003
    Location
    Richmond, VA
    Posts
    11
    Sure thing.

    SELECT *
    FROM CCWines
    WHERE 1 = 1
    IF mnuCategory is not "All"
    AND mnCatagory = mnuCategory
    ENDIF
    IF mnuVarietal is not "All"
    AND mnuVarietal = mnuVarietal
    ENDIF

  7. #7
    Join Date
    Oct 2003
    Posts
    706

    Lightbulb

    What I normally do is to run my own queries and build the list of choices... then put that list of choices into the box instead of relying on an SQL-query to populate it.

    If that doesn't work so good in your case, how about putting a checkbox for "Any" or maybe a radio-button. Enable the drop-down list only when the "Any" choice isn't selected. Users can grok that pretty-much in a flash and it's not hard to implement. "Gets the job done just as well," and sometimes that's the name of the game.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, sorry, i guess the article was not clear enough

    you have an IF/ELSE construction in the SQL that is actually supposed to be outside the sql, in your scripting language

    i am not an access programmer so i do not know how to do that in access -- if you have a form dropdown or whatever, you need to test it to see if the "any"/"all" option has been selected, and then generate the sql accordingly

    does that make sense?

  9. #9
    Join Date
    Oct 2003
    Location
    Richmond, VA
    Posts
    11
    I will try that.

    Thanks again for the input.

Posting Permissions

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