Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2009
    Location
    Google, KS
    Posts
    47

    Unanswered: Query Criteria using Combobox that displays 'ALL'

    I have a combobox that uses the following table/query as the row source:
    Code:
    SELECT Brand
             , Id 
    FROM tbl_VendorNbr 
    Union 
    Select "All"
            , Null 
    FROM tbl_VendorNbr;
    Then I have a query uses the criteria [Forms]![Form1]![Combobox] and works fine as long as something other than 'All' is selected. When 'All' is selected no records are returned by the query when the exact opposite is desired.

    Any ideas? I thought I had it working before lunch and then I come back and it wasn't working. Now I can't seem to wrap my head around it.

    Thanks for the help!
    Life - sexually transmitted, always fatal.

    My beer drunken soul is sadder than all the dead christmas trees in the world.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Probably because nothing ever equals null...

    Lets see the code you're using to filter stuff based on this combobox value.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I suspect you want this technique:

    Queries: Use a parameter to return all records if Null
    Paul

  4. #4
    Join Date
    Nov 2009
    Location
    Google, KS
    Posts
    47
    Here is the query.
    Code:
    SELECT tbl_AllItems.ItemNbr
             , tbl_VendorNbr.Brand
    FROM tbl_AllItems 
    INNER JOIN tbl_VendorNbr 
    ON tbl_AllItems.VendorNbr = tbl_VendorNbr.VendorNbr
    WHERE (((tbl_VendorNbr.Brand)=[Forms]![SWAS_Builder]![CboBranded]));
    Here is the combobox row source.
    Code:
    SELECT Brand, Id 
    FROM tbl_VendorNbr 
    UNION
    SELECT "All", 0 
    FROM tbl_VendorNbr;
    @Teddy - I've tried using '0' as opposed to null. neither worked

    @Pbaldy - I've also tried "OR [Forms]![Swas_Builder]![CboBranded] Is Null" didn't work either

    There are 2 brands, when I select brand A or B in the combobox the query works. When I select all, the query returns 0 records.
    Life - sexually transmitted, always fatal.

    My beer drunken soul is sadder than all the dead christmas trees in the world.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by Marsbars View Post
    Here is the query.
    Code:
    SELECT tbl_AllItems.ItemNbr
             , tbl_VendorNbr.Brand
    FROM tbl_AllItems 
    INNER JOIN tbl_VendorNbr 
    ON tbl_AllItems.VendorNbr = tbl_VendorNbr.VendorNbr
    WHERE (((tbl_VendorNbr.Brand)=[Forms]![SWAS_Builder]![CboBranded]));
    Here is the combobox row source.
    Code:
    SELECT Brand, Id 
    FROM tbl_VendorNbr 
    UNION
    SELECT "All", 0 
    FROM tbl_VendorNbr;
    @Teddy - I've tried using '0' as opposed to null. neither worked

    @Pbaldy - I've also tried "OR [Forms]![Swas_Builder]![CboBranded] Is Null" didn't work either

    There are 2 brands, when I select brand A or B in the combobox the query works. When I select all, the query returns 0 records.
    Substitute the number "0" for [Forms]![SWAS_Builder]![CboBranded] in your query above.

    Notice anything wrong with that?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Nov 2009
    Location
    Google, KS
    Posts
    47
    The substitution did not work.

    The reason I think it did not work is because the query you are refering to is in the combobox's rowsource properties. I've set it up as a UNION query not a UNION ALL. It adds the text 'All' into the combobox and gives it an ID of 0, I could change it to 3 I suppose, but that doesn't solve my problem of returning 'all' records (as if there was no filter.)

    Hope I make sense.
    Life - sexually transmitted, always fatal.

    My beer drunken soul is sadder than all the dead christmas trees in the world.

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The technique I posted expects Null in the bound column of the combo. From the looks of it, that's not what you've got. It appears the bound column is the first one containing the brand.
    Paul

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by Marsbars View Post
    The substitution did not work.

    The reason I think it did not work is because the query you are refering to is in the combobox's rowsource properties. I've set it up as a UNION query not a UNION ALL. It adds the text 'All' into the combobox and gives it an ID of 0, I could change it to 3 I suppose, but that doesn't solve my problem of returning 'all' records (as if there was no filter.)

    Hope I make sense.
    Ok, so you're seeing that asking for records where tbl_VendorNbr.Brand=0 is going to be bunk... ?

    Here's a way to get around that:
    tbl_VendorNbr.Brand = iif([Forms]![Swas_Builder]![CboBranded] = 0, tblVendorNbr.Brand, [Forms]![Swas_Builder]![CboBranded])

    here's the logic:

    I want all records where Brand is equal to the following value:
    - If the value of my combo box is zero, then use the current value of Brand. Effectivley this becomes "WHERE tbl_VendorNbr.Brand = tbl_VendorNbr.Brand", which is ALWAYS true, barring a null value in the table itself, which you could handle with an " OR [field] IS NULL"
    - If the value of my combo box is NOT zero, then use the current value of the combo box. This becomes "WHERE tbl_VendorNbr.Brand = [Forms]![Swas_Builder]![CboBranded]"



    Make sense?

    I use that trick for optional parameters in SQL Server like so:
    WHERE someField = ISNULL(@SOME_FIELD, someField)

    ISNULL() is basically a shortcut for COALESCE(), which returns the first non-null argument...



    You could trim things up by using NULL as the "all" value and using the nz() function which amounts to ISNULL() for access:
    WHERE tbl_VendorNbr.Brand = nz([Forms]![Swas_Builder]![CboBranded], tblVendorNbr.Brand)
    Last edited by Teddy; 01-26-10 at 15:16.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. In the RowSource of the combo, change the UNION like this:
    Code:
    UNION SELECT "All", "'*'" FROM...
    (pay attention, it is double quotes, single quotes, asterisk, single quotes, double quotes)

    2. In the query replace the equal operator by the Like operator:
    Code:
    WHERE (((tbl_VendorNbr.Brand) Like [Forms]![SWAS_Builder]![CboBranded]));
    Have a nice day!

Posting Permissions

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