Impossible? (Cross-posted in Forms forum)

Discussion in 'Microsoft Access Form Coding' started by jennifer_cracchiola@mastercard.com, Jul 31, 2006.

  1. Guest

    I'm probably trying to do the impossible here.

    I want to have a union query in a combo box to select "all" AND have
    that combobox filter off of another.

    I can get the first part of code working, the second part of code
    working, but when I try to join into one SQL statement is bombs out.

    Help!

    Code Snippet for combobox filter:
    SELECT Region_Country_Code_tbl.Country, Region_Country_Code_tbl.Region
    FROM Region_Country_Code_tbl
    WHERE
    (((Region_Country_Code_tbl.Region)=IIf(IsNull([Forms]![Form2]![combo26]),[Region],[Forms]![Form2]![combo26])))
    ORDER BY Region_Country_Code_tbl.Country


    Code Snippet for Union query (to populate option of "all")
    SELECT Region_Country_Code_tbl.Country FROM Region_Country_Code_tbl
    UNION (Select "(All)" as Country From Region_Country_Code_tbl GROUP
    BY Region_Country_Code_tbl.Country);


    And here's where I'm at, which clearly isn't working.
    SELECT Region_Country_Code_tbl.Country, Region_Country_Code_tbl.Region
    FROM Region_Country_Code_tbl UNION (Select "(All)" as
    Region_Country_Code_tbl.Country From Region_Country_Code_tbl, null from
    Region_Country_Code_tbl) where Region_Country_Code_tbl.Region =
    Forms![new_adhoc_maker_frm]![combo292];

    Please help this damsel in distress! Thanks in advance!!!

    Jen
     
    , Jul 31, 2006
    #1
    1. Advertisements

  2. Dirk Goldgar Guest

    <> wrote in message
    news:
    > I'm probably trying to do the impossible here.
    >
    > I want to have a union query in a combo box to select "all" AND have
    > that combobox filter off of another.
    >
    > I can get the first part of code working, the second part of code
    > working, but when I try to join into one SQL statement is bombs out.
    >
    > Help!
    >
    > Code Snippet for combobox filter:
    > SELECT Region_Country_Code_tbl.Country, Region_Country_Code_tbl.Region
    > FROM Region_Country_Code_tbl
    > WHERE
    >

    (((Region_Country_Code_tbl.Region)=IIf(IsNull([Forms]![Form2]![combo26])
    ,[Region],[Forms]![Form2]![combo26])))
    > ORDER BY Region_Country_Code_tbl.Country
    >
    >
    > Code Snippet for Union query (to populate option of "all")
    > SELECT Region_Country_Code_tbl.Country FROM Region_Country_Code_tbl
    > UNION (Select "(All)" as Country From Region_Country_Code_tbl GROUP
    > BY Region_Country_Code_tbl.Country);
    >
    >
    > And here's where I'm at, which clearly isn't working.
    > SELECT Region_Country_Code_tbl.Country, Region_Country_Code_tbl.Region
    > FROM Region_Country_Code_tbl UNION (Select "(All)" as
    > Region_Country_Code_tbl.Country From Region_Country_Code_tbl, null
    > from Region_Country_Code_tbl) where Region_Country_Code_tbl.Region =
    > Forms![new_adhoc_maker_frm]![combo292];
    >
    > Please help this damsel in distress! Thanks in advance!!!


    Did you try

    SELECT
    Country,
    Region
    FROM Region_Country_Code_tbl
    WHERE
    Region = Forms![new_adhoc_maker_frm]![combo292]
    UNION
    SELECT
    "(All)" As Country,
    Null As Region
    FROM
    Region_Country_Code_tbl;

    ?
    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
    Dirk Goldgar, Jul 31, 2006
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Vass
    Replies:
    0
    Views:
    153
  2. Patrick Graham

    Active-X - (posted in other forum but no one answers)

    Patrick Graham, Feb 20, 2004, in forum: Microsoft Access Form Coding
    Replies:
    2
    Views:
    1,015
    Guest
    Feb 21, 2004
  3. Sergio

    Checking if subform is displaying record (also posted on forms)

    Sergio, Apr 13, 2004, in forum: Microsoft Access Form Coding
    Replies:
    1
    Views:
    136
  4. Guest
    Replies:
    1
    Views:
    142
    MacDermott
    Oct 23, 2004
  5. DeepTrouble

    Forms with parameters to pass to cross-tab queries

    DeepTrouble, Mar 6, 2007, in forum: Microsoft Access Form Coding
    Replies:
    3
    Views:
    200
    DeepTrouble
    Mar 9, 2007
Loading...

Share This Page