Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2010
    Posts
    6

    Unanswered: Combo box / Query Criteria Issues

    Hello everyone. This is my first post here. I've never taken any classes on Access 2007 and all my knowledge comes from the HELP file, forums like this and painstaking experience. The wizards are a life saver and as far as VB or SQL goes, I'm pre-kindergarten level.

    So, now that you know my knowledge base, here's my question.

    My goal is to use a form with multiple combo boxes to filter a query and return a subform as well as an option to print a report or export the query to Excel.

    -The form is called Job_Shopper_Frm.
    -In it are four combo boxes tied to premade tables that contain the criteria information for the query.
    -There is also a text box with calendar function for "Date" criteria in the query.
    -The query is called Job_Shopper_Qry.
    -The query pulls from the table @@Master_All_Service_Tbl.

    In the "Department" field of Job_Shopper_Qry is criteria tied to one of the combo boxes in the form Job_Shopper_Frm.

    [Forms]![Job_Shopper_Frm]![Combo_Department]

    And this works great when I've selected a Department from the drop box.

    The problem is, if I don't select an option from the drop box and leave it blank, the query returns a blank result. No records. Now, I understand that this is because there is no Department called "Blank" (you know what I mean).

    How do I get the criteria to return all records if the combo box is blank?

    For your amusement, I tried these today...

    IIf([Forms]![Job_Shopper_Frm]![Combo_Department] is null, is null, [Forms]![Job_Shopper_Frm]![Combo_Department])

    and

    IIf([Forms]![Job_Shopper_Frm]![Combo_Department] = "", is null, [Forms]![Job_Shopper_Frm]![Combo_Department])

    and

    IIf([Forms]![Job_Shopper_Frm]![Combo_Department] = "", "", [Forms]![Job_Shopper_Frm]![Combo_Department])

    None of them worked. I still got one of two returns. No records if no selection is made. Or Records for the selection made.

    I want ALL RECORDS if no selection is made in the combo box.

    Any assistance that anyone could provide would be deeply appreciated.
    Last edited by D3L33T; 06-22-10 at 17:00.

  2. #2
    Join Date
    Jun 2010
    Posts
    6

    An idea!

    I was talking to my wife (an Oracle Superuser) about my Db issue. She suggested using a wildcard. Brilliant! Why didn't I think of that?!?

    So, would this work?

    IIf([Forms]![Job_Shopper_Frm]![Combo_Department] is null, *, [Forms]![Job_Shopper_Frm]![Combo_Department])

    or

    IIf([Forms]![Job_Shopper_Frm]![Combo_Department] = "", *, [Forms]![Job_Shopper_Frm]![Combo_Department])

  3. #3
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1
    Test your combobox for no selection, and query for everything in that case. The test is :

    If combobox.listindex= -1 then ....else....endif

    Comboboxes must always be tested for no selection in any case.

    Regards


    John S

  4. #4
    Join Date
    Jun 2010
    Posts
    6

    Didn't work

    Per your advice, I tried:

    (assuming the syntax is: If [combo.box] = -1 then "wildcard" else [combo.box] endif)

    If Forms![^^The Job Shopper]![Combo0] = -1 then * else Forms![^^The Job Shopper]![Combo0] endif

    Didn't work. (screenshots attached, I think)

    If then function.bmp

    If then error001.bmp

    I looked through the available functions in the expression builder and "If Then Else" was not there. So I tried this next.

    IIf (IsEmpty ([Forms]![^^The Job Shopper]![Combo0]), *, [Forms]![^^The Job Shopper]![Combo0])

    No dice.

    I'll keep working on this, but any help I get would be greatly appreciated.

  5. #5
    Join Date
    Jun 2010
    Posts
    6

    I'm an idiot

    My sincerest appologies praxis1949! I figured out what you meant by an If Then Test. This is what I tried:

    Combo Box Code If Then Test.bmp

    Is it right?

    I'm thinking no because it still didn't work.

    I'll keep cranking away at it. Still need help. Thanks.

  6. #6
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1

    Test on the event...

    If the combobox. listindex returns -1, do a query returning everything, else do a restrictive query (e.g. criteria in query = [forms]![frmChoice]![cboStuCh].[value]). You may have to use two queries; one with something like [forms]![frmChoice]![cboStuCh].[value] and one without any "criteria" (so it returns everything).

    Perhaps this code is of use. The first combobox is used to load a second combo box:

    Private Sub cboPeriod_AfterUpdate()
    Dim strSQL As String
    Dim aValue As Integer

    aValue = Me.cboPeriod.ListIndex + 1

    'the listindex starts at 0; items in our list start at 1


    strSQL = "Select id, period from periods where type = " & aValue & " order by id" 'order by id because data from text files

    Me.cboSpecific.RowSource = strSQL
    Me.cboSpecific.Requery

    End Sub

    Perhaps I have reached (or exceeded) the edge of my incompetence!

    Regards

    John S
    (currently) Guangxi University,
    Nanning China

  7. #7
    Join Date
    Jun 2010
    Posts
    6

    Thank you

    I'll take some time to decipher and interpret what you've suggested. Once I can wrap my brain around it I'll give it a try and report back my results.

  8. #8
    Join Date
    Jun 2010
    Posts
    6

    Still no dice

    I tried everything praxis1949 suggested and still no dice. It may be that my knowledge of VB and macros is not sufficient. Any other ideas? I still got nothing. I don't want to give up on this project as a hopeless quest.

  9. #9
    Join Date
    Feb 2012
    Posts
    1

    works?

    I suspect you want this technique:

    Queries: Use a parameter to return all records if Null

Posting Permissions

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