Filter Form with Like * but Null's excluded also

[bignose2]

Hi,

Access 2007. Form.

I am trying to filter a form so that it does NOT show items selected from a list box. Might be too much to ask

Sort of works well and v.nearly there but also hides the record if the "Details" field I am filtering on is null (Seems to be null, "" seems OK, I would prefer not to modify the table from null to "")

tried a few things like "nz([CoDetails],"") Not Like '" & "*" .....
but not work & think wrong in principle here.

Any ideas, thanks I/A

Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![List0].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " AND "
End If
Criteria = Criteria & "[CoDetails] Not Like '" & "*" & Me![List0].ItemData(i) & "*" & "'"

Next i

' Filter the form using selected items in the list box.
Me.Filter = Criteria
Me.FilterOn = True

It is actually a bank statement and I want to filter/hide things like
"DUALITY" and a few others but there are blank(null) details fields also I don't want to hide.

BRITISH TELECOM
8941LINK17:00NOV02
TALKTALK LIMITED
DUALITY-E 011112
SERVICE CHARGE
COMMISSION
F

DUALITY-E 031112
DUALITY-E 021112
DUALITY-E 041112
DUALITY-E 051112

 

[Brianwarnock]

Like does not return nulls, you will have to check for them.

 

[bignose2]

Hi,

not sure how I can check for nulls in the criteria above

The only thing, not yet tried, can I then add another criterea on top of the main one, Seperate statement immediately after this one, to included any records with NULL in the details field.

 

[Brianwarnock]

I no longer have Access to try but you should be able to build
Or codetails is null at the end of your criteria or maybe start with
CoDetails is Null or


Brian