Filtering for null or empty field

[TechieJr]

Hello all,

It's been a long while since I've developed a new database and have forgotten a lot of tips I learned years ago, so forgive me if this seems obvious.

I have a table which has a "Level" field which stores the sponsorship level of a sponsoring business. I have "Silver", "Gold" and "Platinum". I have buttons in my header which filter the records shown in the detail. Clicking on Silver shows all the silver level supporters, etc. I have a button which shows all the businesses approached. I have another button which shows all businesses which show those businesses which has sent in pledges. I cannot seem to figure out how to set a filter to show those businesses which have not sent in a pledge. I have tried the same filter code for the other buttons but have used "Null", "False" and "" as the criteria, but no records have ever been returned. What am I doing wrong?

TIA

 

[dhookom]

It isn’t clear how you are applying any filtering. Are you using VBA to update the record source SQL or updating the Filter property or other?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TechieJr]

Sorry for the incomplete and unclear post. Last minute thing at the end of an overly busy day.

I am using an embedded macro in the "ON Click" Event of a button in the header to update and apply the Filter property.

Thanks

 

[dhookom]

I don’t use macros however the result of the filter would need to be:
Level Is Null

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TechieJr]

Thanks dhookom, that's what I expected and have been trying, however it is not bringing up any results.
In the "On Click" Event for the button, I have:

ApplyFilter

Filter Name: No Sponsorship​

Where Condition =: [Level]=Null​

Control Name :​

This is the very same code I use for the other buttons which filter out the Silver, Gold, and Platinum, which work perfectly. For the "All Sponsors" I use the condition "True" instead of a color. However in trying to filter out just those who have not sent in a sponsorship, it is not working. Out of desperation, I have also tried "False" and "" in addition to "Null".

What am I missing?

 

[dhookom]

You can’t use = with Null. That’s why I suggested
Is Null

You could convert the Null to a string with:
[Level] & "" = ""

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Remember, nothing equals to NULL, not even a NULL

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TechieJr]

I think I'm getting too old to develop a new database. Thank you dhookom, once I read your answer properly, and put the code in correctly, it worked. Thank you for your patience.

BTW, I remember you replying to some of my threads 20 years ago when I was working on another database. Appreciated you then, and I still do.

 

[dhookom]

Glad to be of some help 20 years ago and now. I’ve enjoyed every bit of it.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016