Query if a field is null 1

[ptrifile]

I have a query that has its criteria based off of a form. I have three fields. Main_category, Second_category and Third_category. All pointing to those fields on FrmMain.

This works fine if all three fields are populated but sometimes the third_category will be blank or null but when that happens the query comes back with no records when i need it to still match records that only have a Main and Second category.

Can anyone steer me in the right direction or give me some possible solutions?

Thanks in advance!

Paul

 

[ptrifile]

Thanks to anyone who was looking at this but I have figured it out. In case anyone has a similar issue I used the following query:

SELECT *
FROM tbllinks
WHERE (((IIf(IsNull([Forms]![frmmain]![main_category]),True,[main_category] Like [Forms]![frmmain]![main_category]))<>False) AND ((IIf(IsNull([Forms]![frmmain]![second_category]),True,[second_category] Like [Forms]![frmmain]![second_category]))<>False) AND ((IIf(IsNull([Forms]![frmmain]![third_category]),True,[third_category] Like [Forms]![frmmain]![third_category]))<>False));

 

[dhookom]

The solution I see recommended most often is:

SELECT *
FROM tbllinks
WHERE ([main_category]   Like [Forms]![frmmain]![main_category]   OR [Forms]![frmmain]![main_category]   Is Null)
  AND ([second_category] Like [Forms]![frmmain]![second_category] OR [Forms]![frmmain]![second_category] Is Null)
  AND ([Third_category]  Like [Forms]![frmmain]![Third_category]  OR [Forms]![frmmain]![third_category]  Is Null)

Duane
Hook'D on Access
MS Access MVP

 

[ptrifile]

Thanks dhookum! I ended up using your solution in the end!