problems with blank text field in filter

[Delindan]

I have a form that has a combo box to select criteria for a filter. The combo pulls down unique values from the table. The problem happens when the user selects a blank from the combo.

This is the code where I'm using the result from the combo box.

Case intSelss
Set DB = CurrentDb
Set qdf = DB.CreateQueryDef("", "Select * From [Summary of fx and oh] Where [Staffing Status] = Forms![Dollar Export with fx and oh]![Cboss]")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set RSBudget = qdf.OpenRecordset

So in this case if they choose staffing status = blank it comes up with no results. Any ideas?

Thanks!

 

[Andrzejek]

Do you want to have blank in your combo for users to be able to select it?

If so, how about:
[tt]
Case intSelss
Set DB = CurrentDb
If Forms![Dollar Export with fx and oh]![Cboss] = "" Then
strSQL = "Select * From [Summary of fx and oh] Where [Staffing Status] = [red]''[/red]"
Else
strSQL = "Select * From [Summary of fx and oh] Where [Staffing Status] = Forms![Dollar Export with fx and oh]![Cboss]"
End If
Set qdf = DB.CreateQueryDef("", strSQL)
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set RSBudget = qdf.OpenRecordset
[/tt]

Have fun.

---- Andy

 

[dhookom]

I hate coping with parameters. Try this assuming Staffing Status is numeric:

    Dim strSQL as String
    strSQL = "SELECT * FROM [Summary of fx and oh] Where 1=1 "
    Case intSelss
        Set DB = CurrentDb
        If NotIsNull(Me.CboSS) Then
            strSQL = strSQL & " AND [Staffing Status] = " & Me.cboSS
        End If
[green]        ' more similar chunks for other controls if needed[/green]
        Set qdf = DB.CreateQueryDef("", strSQL)
        Set RSBudget = qdf.OpenRecordset

Duane
Hook'D on Access
MS Access MVP

 

[Delindan]

Staffing Status is text and the combo box is populated from the actual choices in the table...one choice being if that field is blank. So there are records with that field blank however when you choose that from the combo it doesn't come up with matches.

Thanks

 

[dhookom]

    Dim strSQL as String
    strSQL = "SELECT * FROM [Summary of fx and oh] Where 1=1 "
    Case intSelss
        Set DB = CurrentDb
        strSQL = strSQL & " AND [Staffing Status] & '' = '' & '" & Me.cboSS & "'"
        ' more similar chunks for other controls if needed
        Set qdf = DB.CreateQueryDef("", strSQL)
        Set RSBudget = qdf.OpenRecordset

Duane
Hook'D on Access
MS Access MVP

 

[Delindan]

That worked perfect...thanks!

 

[Delindan]

So after going through this there is one thing I don't understand. What does where 1=1 do in the line: strSQL = "SELECT * FROM [Summary of fx and oh] Where 1=1 "

 

[dhookom]

The " 1=1 " is a method for creating where conditions and combining them all using " AND ...". It's just a place holder.

Duane
Hook'D on Access
MS Access MVP

 

[Delindan]

Thanks!