Query to filter resluts based on Combo Box

[jereece]

I have a query to filter the results of my data based on a combo box located on a form. The form Combo Box is called cbo_Status. The code I have in the Criteria section of the query is

[Forms]![frm_Main]![cbo_Status] Or Like [Forms]![frm_Main]![cbo_Status] & "*"

When I run the query, with a status selected in the Combo Box the proper results are returned. However when the Combo Box is empty, only records that have data in the Status field of the data table are returned. When the Status Combo Box is empty, I want all the data to be displayed in the query results. I have used this method before but for some reason it's not working.

Any suggestions?

Thanks,
Jim

 

[ByteMyzer]

You might try something along these lines (substitute highlighted text with actual names):

WHERE ( [Forms]![[B][I]frm_Main[/I][/B]]![[B][I]cbo_Status[/I][/B]] Is Null ) OR
      ( [Forms]![[B][I]frm_Main[/I][/B]]![[B][I]cbo_Status[/I][/B]] Is Not Null AND
        ( [B][I]MyTable.MyField[/I][/B] = [Forms]![[B][I]frm_Main[/I][/B]]![[B][I]cbo_Status[/I][/B]] OR
          [B][I]MyTable.MyField[/I][/B] Like [Forms]![[B][I]frm_Main[/I][/B]]![[B][I]cbo_Status[/I][/B]] & '*'
        )
      )

 

[jereece]

Thanks. That will work.

 

[Atomic Shrimp]

Alternatively, this might work:

Like (iif(isnull([Forms]![frm_Main]![cbo_Status]), "*", [Forms]![frm_Main]![cbo_Status]))

(untested air code)