Dealing with Null values in filter

[davesmith202]

If I want to build a string for a filter, how do I cater for cases where the value is null or no value? (By the way, is does null="" or is it slightly different?)

e.g.

strMyFilter="MgtReview='In Progress' or MgtReview=''"

Would that give MgtReview for In Progress or Null? Or do I need something like

strMyFilter=""MgtReview='In Progress' or MgtReview=Null"

Thanks,

Dave

 

[The_Doc_Man]

(By the way, is does null="" or is it slightly different?)

Null is a string that has not been defined at all yet. "" is a string that has been defined as empty. You can take the approach of looking for both cases separately. Try Help on the IsEmpty and IsNull functions.

IsNull( null string ) will be TRUE. IsEmpty( empty string ) will be TRUE. The cross-cases will not. However, there is an easier way. Look up function Nz in the Help Files. This function allows you to specify a default value for a field suspected of sometimes being null.

IsEmpty( Nz( [myfield], "" ) ) will be TRUE if [myfield] is null OR empty.

Once you have the results of that test, you can do a proper if-then-else treatment of [myfield]. Good luck in your project!

 

[Pat Hartman]

Null is not a string. A numeric field may be null or a text field may be null. However, a numeric field may NOT contain a zero-length string since that IS a string. You cannot test for " = Null " since that condition will NEVER return True regardless of the value of your field. If you want to test for null values, use "If IsNull(MyFld)" in VBA or "Where MyFld Is Null" in SQL.

I would use Doc's suggestion when dealing with controls on forms since these are variants and therefore may be null or zero-length strings.

 

[davesmith202]

Thanks for clarifying the Null verses zero length string.

Can I do something like this?

strMgtReview = "MgtReview='Not Reviewed' or MgtReview='In Progress'or MgtReview='Finished' or MgtReview='' or Where MgtReview Is Null"
Me.frmTasksSub.Form.Filter = strMgtReview
Me.frmTasksSub.Form.FilterOn = True

 

[Pat Hartman]

A filter does NOT contain the "where". It only contains what comes after -

strMgtReview = "MgtReview = 'Not Reviewed' or MgtReview = 'In Progress' or MgtReview = 'Finished' or MgtReview Is Null"

or if you prefer

strMgtReview = "MgtReview = 'Not Reviewed' or MgtReview = 'In Progress' or MgtReview = 'Finished' or IsNull(MgtReview)"