Run a query based on a combo box that might be blank..

[HairyArse]

Hey guys, I have query I'm trying to run that takes a parameter from a combo box on a form.

If this combo box has an entry selected I want the query to use that as a parameter, if it's blank I want the query to show all.

I've tried:

=Nz('[Forms].[Snapshot].[LiveInitials]','##')

but to no avail. I've also tried %% instead of # and tried without '' around the form reference. I keep getting a data mismatch error.

Live initials is a lookup of a table called StaffMember - we only have 20 staff members so the ID is never more than two digits.

Any suggestions?

 

[namliam]

In your existing column use... only...
[Forms].[Snapshot].[LiveInitials]

Then add a new column with [Forms].[Snapshot].[LiveInitials]
And in the criteria: is null
But on the second line not on the same line of the existing column criteria...

 

[Access_guy49]

I know this doesn't explicitly answer your question, but I have created a search form for many of my databases. I was trying to find a way to create a dynamic search form in which a user could select one combobox or several comboboxes and the search would be filtered based on those criteria. As a result I had to find code that would build a filter. This code I found on the microsoft website (sorry i don't have a better source) and then i changed it to work for my needs. It does allow for blank comboboxes though

Private Sub btn_SAR_Click()
    Dim stDocName As String
    stDocName = "My Form Name"
    On Error Resume Next
    
    Dim ctl As Control
    Dim sSQL As String
    Dim sWhereClause As String
    
    'Initialize the Where Clause variable.
    sWhereClause = " Where "
    
    'Start the first part of the select statement.
    sSQL = "select * from NameOfTableToSearchOn"
    
    'Loop through each control on the form to get its value.
    For Each ctl In Me.Controls
        With ctl
            'The only Control you are using is the text box.
            'However, you can add as many types of controls as you want.
            Select Case .ControlType
                Case acComboBox
                    .SetFocus
                    'This is the function that actually builds
                    'the clause.
                    If sWhereClause = " Where " Then
                        sWhereClause = sWhereClause & BuildCriteria(.Name, dbText, .Text)
                    Else
                        sWhereClause = sWhereClause & " and " & BuildCriteria(.Name, dbText, .Text)
                    End If
            End Select
        End With
    Next ctl
    
    DoCmd.OpenForm stDocName 'open the form
    'This opens the form and then displays only the filtered records
    Form_MyFormToOpen.RecordSource = sSQL & sWhereClause
    Form_MyFormToOpen.Requery
    
End Sub

You might be able to use.