Filter form where field value is Null 1

[addy]

Please help, this is driving me insane.

I have a button on my form which when clicked, it should filter my form. I want to filter it to show all records where a particular field is Null.

I have tried every possible combination I can think of for the DoCmd.ApplyFilter method but I cannot get it to work where a field value is Null.

In Form design view, if I put:

PrimaryAllocation = ""

in the Filter criteria under the Data tab it works.

However, I need it to run from a button click, I have tried:

DoCmd.Applyfilter, "PrimaryAllocation = Null"
DoCmd.Applyfilter, "PrimaryAllocation = 'Null'"
DoCmd.Applyfilter, "PrimaryAllocation is Null"
DoCmd.Applyfilter, "IsNull(PrimaryAllocation)"

etc. etc.

How do I apply a form filter where a field value is Null?

This is really beginning to wind me up!

 

[PHV]

what about something like this ?

Me.Filter = "Trim(PrimaryAllocation & '') = ''"
Me.FilterOn = True

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[addy]

As always PHV - your advice works perfectly.

Thanks!