Query problem filtering based on another combo box

[marcin2k]

I have two combo boxes and one is suppose to limit the records based on the other. So if I choose in my combo box called Combo1 "Red" I want to see all records which show something like "Red" in one of my fields called Field1. HEre is what I have now.

Field1:
Like IIf(IsNull([Forms]![fForm1]![Combo1]),"*",[Forms]![fForm1]![Combo1])

The problem is that it displays all records correctly when this field is not null but when it is null it does not display records that have no value in this field. So basically if the Combo1 is null it displays all records which are not null and I need it to display everything including records which have something in this field and does that are null.

 

[hneal98]

That is a problem with nulls. You have to test specifically for the nulls.

 

[dhookom]

If you are setting your criteria on a field named [Color], you can change the Field to:
Field: TheColor: [Color] & ""

This will convert the Null colors to a zero-length-string so your criteria will work as desired.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

Another way:
Criteria: [Forms]![fForm1]![Combo1] OR [Forms]![fForm1]![Combo1] Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[marcin2k]

Thanks for the help, adding & "" worked great

 

[stephenk1973]

On the update of the event build a sting to set as the soruce of your form. Set an if statement to add in the where clause on the combo box, only if it is not null.


Dim strsql As String

Set db = CurrentDb()

strSQl= "SELECT FIELD1, FIELD2 "
strsql = strsql & " FROM TABLE "

If(IsNull([Forms]![fForm1]![Combo1]) then
'Don't add anything
Else
'Do add something
strsql = strsql & "WHERE FIELD1='" &([Forms]![fForm1]![Combo1]) & "' "
End if

strSql =";"

Me.RecordSource = strSQL


Hope this helps

Stephen