Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009
    Posts
    1

    Question Unanswered: Query By Form: ComboBox Problem

    Access 2003
    WinXP

    I’m working on a fire department Run database for the local volunteer fire department. They will log their daily run sheets into the database then query it via a form.

    With a few online tutorials I’ve managed to get a simple query by form to work for exact matches, but that’s not all I need. The form launches a macro that I believe launches the query. Odd to need a 3rd Party system (the macro) but it seems to work.

    Database has a field called ‘Shifts’ currently populated with either “A-Shift” or “B-Shift” or “C-Shift” or Null (records with Null will be populated to show proper shift at a later date).

    Form has a combo-box called Shifts with “All”;”A-Shift”;”B-Shift”;”C-Shift” as the values. Naturally with either of the shifts selected it returns a report correctly but how do I get it to show all records when “All” is selected?

    The criteria in the query currently is: [Forms]![FormName]![Shift]

    I tried using an Immediate If statement (IIF) but it returned that it was too complicated and halted.

    I thought about trying to do a if then statement in the form before the values were sent like so:

    IF
    [Shift] = “All”
    THEN
    [Shift] = “A-Shift” OR “B-Shift” OR “C-Shift” OR NULL
    ENDIF

    But don’t know enough about expressions or VBA to do it.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You do not need to specify any criteria at all in case you want to select all records.
    Have a nice day!

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    The criteria in the query currently is: [Forms]![FormName]![Shift]
    Change that to something like this:

    Shift Like IIf(Forms!dlgReporting!cboShift <> "All",Forms!dlgReporting!cboShift,"*")

    BTW, there is a naming conflict here. The combo box that holds the All etc for the user to select can't be the same name as the field you are using criteria on (Shift).

    DON'T give up on IIF just because you hit a snag! You should have pasted your IIF function so you could have learned WHY it was failing!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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