criteria expression? (1 Viewer)

[pungentSapling]

why does this expression not work?
it is the criteria in a query

I am trying to make the query return everything in the field if nothing is selected in the combo box where the criteria comes from.

IIf(IsNull([Forms]![frmVendors]![cboComponent]),"",([Vendor].[Component]) Like "*" & [Forms]![frmVendors]![cboComponent] & "*")

it is not giving errors but it comes up with no results


oyy my head

this much works
Like "*" & [Forms]![frmVendors]![cboComponent] & "*"

 

[Krysti]

I may not be understanding exactly what you're trying to do, so if I am off base, just ignore my response

Try putting in the criteria your combo box name and then underneath that in the next criteria (for the OR) put in your combo box name and Is Null:

[Forms]![frmVendors]![cboComponent]
[Forms]![frmVendors]![cboComponent] Is Null

That way if something is selected in the combo box, it should return that record. Otherwise, if nothing is selected it should return all records.

Hope this is what you're looking for!

Krysti

 

[pungentSapling]

thanks for the reply I found another way to accomplish what I was after..


Private Sub cboVendor_GotFocus()
If IsNull(Me.cboComponent.Value) Then
Me.cboVendor.RowSourceType = "Table/Query"
Me.cboVendor.RowSource = "tblVendor"
Me.cboVendor.ColumnCount = "3"
Me.cboVendor.ColumnWidths = "0,0,1"
Else
Me.cboVendor.RowSourceType = "Table/Query"
Me.cboVendor.RowSource = "qryLimit"
Me.cboVendor.ColumnCount = "2"
Me.cboVendor.ColumnWidths = "1,0"
End If
End Sub
this way if the combo box is null then the other cboBox uses a different RowSource....the original table instead of the dynaset
your way would certainly have saved a lot of typing....I will try it