Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2005
    Posts
    28

    Question Unanswered: IIF Statement for null criteria

    I have 3 combo boxes on a form that allow the user to select criteria to search for stock in my tblStock.

    Supplier
    StockType
    StockCategory

    They do not have to use all combo boxes, for example they could search using only supplier, or alternatively they could search for a particular stock type from a specific supplier.

    I am using a query that takes its criteria from these combo boxes and pulls data from tblStock. I need to use an IIF statement in the criteria of the query so that if one of the combo boxes is left empty it ignores that particular field. The following isn't working, ANY IDEAS?

    IIF([Forms]![frmMainMenu]![frmSearchManufacturer]![StockType] Is Null,"" [Forms]![frmMainMenu]![frmSearchManufacturer]![StockType)

    Thanks
    John

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You're missing a comma after ""

    You could use nz() too...
    oh yeah... documentation... I have heard of that.

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

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Set your combo boxes' SQL in VBA. For example, you might put in the Supplier's AfterUpdate event as follows:

    Code:
        Me.StockCategory.RowSource = "SELECT * FROM TableName WHERE Me.Supplier = '" & Me.Supplier.Value & "'"
    
        If Not IsNull(Me.StockType.Value) Then
            Me.StockCategory.RowSource = Me.StockCategory.RowSource & " And StockType = '" & Me.StockType.Value & "'"
        End If
    
        Me.StockCategory.RowSource = Me.StockCategory.RowSource & ";"
    You can put similar code in the StockType's AfterUpdate event and/or the StockCategory's AfterUpdate event.

    In the form's OnCurrent event, you will want to put the original SQL statement or Query name for each combo box's RowSource so that each user will get a clean combo box to work from.

    HTH,

    Sam

Posting Permissions

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