Null field

[thart21]

Need some help with this one:

I have a "build a query form" with five listboxes. The last one is a list of suppliers. I have the code set to pull the records for only the supplier that is selected or, if nothing is selected, pull all of the records. The problem is that some records do not have a supplier entered in the table and these are not showing up. Where would I put an "OR IsNull" statement to pull these?

For Each varItem In Me.lstSupplier.ItemsSelected
strSupplier = strSupplier & ",'" & Me.lstSupplier.ItemData(varItem) _
& "'"
Next varItem
If Len(strSupplier) = 0 Then
strSupplier = "Like '*' "

Else
strSupplier = Right(strSupplier, Len(strSupplier) - 1)
strSupplier = "IN(" & strSupplier & ")"
End If



strSQL = "SELECT qryAllSavings_Crosstab.* FROM qryAllSavings_Crosstab " & _
"WHERE qryAllSavings_Crosstab.[status] " & strStatus & _
"AND qryAllSavings_Crosstab.[conflevel] " & strConfidence & _
"AND qryAllSavings_Crosstab.[class] " & strProdType & _
"AND qryAllSavings_Crosstab.[category] " & strCategory & _
"AND qryAllSavings_Crosstab.[currentsupplier] " & strSupplier & ";"

Thanks for any advice - I have tried "Like '*' OR IsNull".

Toni

 

[pdx_man]

Pull your field up into your string:

For Each varItem In Me.lstSupplier.ItemsSelected
strSupplier = strSupplier & ",'" & Me.lstSupplier.ItemData(varItem) _
& "'"
Next varItem
If Len(strSupplier) = 0 Then
strSupplier = ""

Else
strSupplier = Right(strSupplier, Len(strSupplier) - 1)
strSupplier = " AND qryAllSavings_Crosstab.[currentsupplier] IN (" & strSupplier & ")"
End If



strSQL = "SELECT qryAllSavings_Crosstab.* FROM qryAllSavings_Crosstab " & _
"WHERE qryAllSavings_Crosstab.[status] " & strStatus & _
"AND qryAllSavings_Crosstab.[conflevel] " & strConfidence & _
"AND qryAllSavings_Crosstab.[class] " & strProdType & _
"AND qryAllSavings_Crosstab.[category] " & strCategory & strSupplier