Results 1 to 10 of 10
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Accepting data in a Where or Having Clause (Access 2000)

    I am using a simple select query (shown below) to select data from a table where the values for RrmCostElem are in ("P","G","O","F"). Is there a way to accept data into the where clause through a combo box or via radio buttons to permit the user to select which fields, P, G, O, or F or combinations of these fields are used? Any ideas?




    SELECT [Flat Res Det Monthly].RrmLink, [Flat Res Det Monthly].RrmYYYYMM, [Flat Res Det Monthly].RrmValue, [Flat Res Det Monthly].RrmCostElem, [Flat Res Det Monthly].RrmValueType
    FROM [Flat Res Det Monthly]
    WHERE ((([Flat Res Det Monthly].RrmCostElem) In ("P","G","O","F")) AND (([Flat Res Det Monthly].RrmValueType)="E"));
    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Regards,

    Gary
    (It's been a while!)

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Accepting data in a Where or Having Clause (Access 2000)

    This has always been a pain. The cleanest way is to use a frontend form to allow the user to select the options from a listbox, then build the SQL statement in code.

    Alternatively, with known number of choices, you can substitute prompts for the allowed respones, putting each in brackets like this:
    ... in ([First choice], [Second choice], [Third choice], [Fourth choice]) ...

    This will stop 4 times to ask for a user entry. If user only has 2 choices, he can just tap-through the last 2.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Accepting data in a Where or Having Clause (Access 2000)

    Thanks Mark.

    I will set up a form to do this on the front end. Is there a way to set up a parameter query that uses a list box or combo box to accept data?
    Regards,

    Gary
    (It's been a while!)

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Accepting data in a Where or Having Clause (Access 2000)

    >>. Is there a way to set up a parameter query that uses a list box or combo box to accept data?<<

    Unfortunately, No. (or at least none I've ever heard about) This is one area that has bugged us all at one time or another.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Accepting data in a Where or Having Clause (Access 2000)

    Mark,

    Thanks for the input. I did not substitute the prompts as requested for the allowed responses (the users did not like this). Instead, I plan to add radio buttons on a form to allow the user to select the parameters. A Command button will use an event procedure keyed on which radio buttons are selected will run the correct Macro.

    I may try to set up a pop up custom dialog box with preset selections.

    In either event, thanks for your help. <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>
    Regards,

    Gary
    (It's been a while!)

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Accepting data in a Where or Having Clause (Access 2000)

    That sounds like a good solution. If you have a limited number of possible choices and you aren't likely to add/change the choices, then the radio buttons is a nice, user-friendly touch.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Accepting data in a Where or Having Clause (Access 2000)

    Technically this isn't a parameter query, but we frequently use one or more combo boxes where we can limit the choices to a reasonable number to construct either AND or OR conditions. Then we simply reference the field in the appropriate criteria area and run the query from the form. List boxes get more complicated because you can do multiple selects to get OR conditions - there we would always build the SQL in code. Hope this helps.
    Wendell

  8. #8
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Accepting data in a Where or Having Clause (Access 2000)

    Mark,

    I did run into something interesting. I have created the Pivot table report several ways. The first is using radio buttons in a modal popup form that launched via a macro on a command button. However, when the pivot table was created, I received a message that the form could not be found even though the form was already closed. Upon selecting OK, the pivot table could be accessed with the correct data. Thinking that the Popup was causing a problem, I recreated the radio buttons on the main startup form so the Popup mode was not used. I received the message again upon creation of the pivot table. Again, after selecting OK, the pivot table worked as expected.

    Even though the radio buttons worked as expected, I could not put out the application with the message. Ultimately, I created a list box and used that to create the Pivot table and did not receive the errors.

    Thanks again for your input.

    Have you encountered this problem in the past?
    Regards,

    Gary
    (It's been a while!)

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Accepting data in a Where or Having Clause (Access 2000)

    >>Have you encountered this problem in the past?<<

    I haven't, but then I haven't tried to do exactly what you are doing.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Accepting data in a Where or Having Clause (Access 2000)

    Well yeah, that's what I do too, and is what I suggested doing in my first response. He then asked if there was a way to use a parameter query and have Access show a combo box.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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