Field query no nulls and only ones with match values

[Ruegen]

I have a form being filtered by multiple IDs that match



However sometimes the field is null

I don't want to show fields that are not null because that gives me all fields for some reason

I only want IDs 1,2,3 and nothing else including no null fields.

Not sure what I have wrong.

[ranman256]

You have to test for null and ignore the control ....
Test all controls for a possible filter then build the where clause.

if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
if not IsNull(chkContact) then sWhere = sWhere & " and [Contact]=" & chkContact.value


    'remove 1st And
sWhere= mid(sWhere,5)


  'just use the filter


me.filter = sWhere
me.filterOn = true

[Ruegen]

I have a form being filtered by multiple IDs that match

However sometimes the field is null

I don't want to show fields that are not null because that gives me all fields for some reason

I only want IDs 1,2,3 and nothing else including no null fields.

Not sure what I have wrong.

It's not a combo box or text field - it's a listbox.

I for loop through it building a where sting "[CALLBACKDATE]=" & strIDs

the strIDs is made up of the IDs

so "23 or 45 or 55"

(the trailing or is cut off)

But when I add the not is null to the where it pulls up all records that are not null and just the ones I want.

[Ruegen]

Here's an example of what I have

It's showing results that are not null as well as the ones I don't want

SELECT tblAreas.AreasID, tblAreas.Area, tblAreas.TourOrganiserID
FROM tblAreas
WHERE (((tblAreas.Area) Like "*vic*") AND ((tblAreas.TourOrganiserID)=2) AND ((tblAreas.[AreaYearID])=17 Or (tblAreas.[AreaYearID])=16)) OR (((tblAreas.[AreaYearID]) Is Not Null));

[orange]

This part of your criteria is returning all non null AreaYearID records

....OR (((tblAreas.[AreaYearID]) IS NOT NULL));

You might try the following sql (but I'm having difficulty understanding this line)

It's showing results that are not null as well as the ones I don't want

SELECT tblAreas.AreasID
    ,tblAreas.Area
    ,tblAreas.TourOrganiserID
FROM tblAreas
WHERE (
        ((tblAreas.Area) LIKE "*vic*")
        AND ((tblAreas.TourOrganiserID) = 2)
        AND (tblAreas.[AreaYearID]) IN (16,17)
      )
    OR ((tblAreas.[AreaYearID]) IS NULL);