Combo Boxes

[Frank72]

Hows it going

This is about the 10th time i've posted this question regarding leaving a combo box unselected so that as a parameter it will select all from the field.So far i've had many responses, many thanks, but unfortunately none seem to be working. I'm sure its got something to do with putting IS NULL in with the criteria box of the query.

I have 3 combos; gender,location and model each a parameter for a query, can i leave one , two or even all three empty in order to take the filter out and therefore select all records (all male, or all loc tec.)? They are all single column combos.

There has to be a simple way of doing this.
Any suggestions pleeeeeeeeeeease give us a shout
thanks alot

Frank

 

[Maquis]

Frank, I've done this many times with my forms. Basically I have a button for the user to push to run the report/query/form etc that pulls from the combo boxes. Here is some code that I use in one of my databases. You should be able to tweak it to fit your fields. (In my form there are 2 textboxes and 4 combo boxes to be evaluated)

    Dim FormName As String
    Dim LinkFilter As String
    Dim dQuote As String
    
    If (Me!BeginDate > Me!EndDate) And (Not (IsNull(Me!EndDate))) Then
      MsgBox "Error: Beginning date cannot be later than Ending date!", vbCritical
      Me!BeginDate.SetFocus
      Exit Sub
    End If
    
    LinkFilter = ""
    dQuote = Chr$(34)
    If Not (IsNull(Me!Broker)) Then
        If LinkFilter = "" Then
            LinkFilter = "[Broker Id] = " & dQuote & Me!Broker & dQuote
        Else
            LinkFilter = LinkFilter & "And [Broker Id] = " & dQuote & Me!Broker & dQuote
        End If
    End If
    
    If Not (IsNull(Me!Customer)) Then
        If LinkFilter = "" Then
            LinkFilter = "[Customer Id] = " & dQuote & Me!Customer & dQuote
        Else
            LinkFilter = LinkFilter & "And [Customer Id] = " & dQuote & Me!Customer & dQuote
        End If
    End If
    
    If Not (IsNull(Me!Company)) Then
        If LinkFilter = "" Then
            LinkFilter = "[Company] = " & dQuote & Me!Company & dQuote
        Else
            LinkFilter = LinkFilter & "And [Company] = " & dQuote & Me!Company & dQuote
        End If
    End If
    
    If Not (IsNull(Me!Rate)) Then
        If LinkFilter = "" Then
            LinkFilter = "[Rate] = " & Me!Rate
        Else
            LinkFilter = LinkFilter & "And [Rate] = " & Me!Rate
        End If
    End If

    If Not (IsNull(Me!BeginDate)) Then
        If LinkFilter = "" Then
            LinkFilter = "[Period Date] >= #" & Me!BeginDate & "#"
        Else
            LinkFilter = LinkFilter & "And [Period Date] >= #" & Me!BeginDate & "#"
        End If
    End If
    
    If Not (IsNull(Me!EndDate)) Then
        If LinkFilter = "" Then
            LinkFilter = &quot;[Period Date] <= #&quot; & Me!EndDate & &quot;#&quot;
        Else
            LinkFilter = LinkFilter & &quot;And [Period Date] <= #&quot; & Me!EndDate & &quot;#&quot;
        End If
    End If
    
    If Me!Rate.Visible = True Then
        FormName = &quot;PaymentDisplay&quot;
    Else
        FormName = &quot;CustomerDisplay&quot;
    End If
    DoCmd.OpenForm FormName, acFormDS, , LinkFilter

Maq B-)
<insert witty signature here>