Filter a report for null? (Office XP)

Topic

New Reply

I want to filter my report to display all records with null values in the “Completed On” column….here’s what I’ve *started* working with…I need a little nudge in the right direction – Thanks!

Dim stDocName As String
Dim strCriteria As String
stDocName = “AwardsMatrix”
strCriteria = “[Completed On]= 0”

DoCmd.OpenReport “AwardsMatrix”, acPreview, acReadOnly, strCriteria

Reply | Quote

Replies

Null is different from zero (0). Zero is a specific number, while null means that data are missing. Don’t think of null as “nothing” but as “it could be anything”. Therefore, you cannot check for null values by using

[Completed On]=0

as criteria, neither by using

[Completed On]=Null

To test for nulls, you must use

[Completed On] Is Null

“Is Null” is a fixed expression (to test for non-null values, you would use another fixed expression “Is Not Null”). So try

strCriteria = “[Completed On] Is Null”

Reply | Quote

I had tried Is Null….but I’d done it like this: IsNull, messing me up….thanks a lot!

Reply | Quote

I admit it is slightly confusing. In a query, and hence in WhereCondition arguments, you use Is Null:

[FieldName] Is Null

but in expressions, for example in the control source of a text box on a form, or in VBA code, you use the function IsNull:

=IIf(IsNull([FieldName]),"This","That")

Reply | Quote

Thanks for the info! There is a LOT to learn about these programs….geez

Reply | Quote

Thanks for the info! There is a LOT to learn about these programs….geez

Reply | Quote

I admit it is slightly confusing. In a query, and hence in WhereCondition arguments, you use Is Null:

[FieldName] Is Null

but in expressions, for example in the control source of a text box on a form, or in VBA code, you use the function IsNull:

=IIf(IsNull([FieldName]),"This","That")

Reply | Quote

I had tried Is Null….but I’d done it like this: IsNull, messing me up….thanks a lot!

Reply | Quote

Null is different from zero (0). Zero is a specific number, while null means that data are missing. Don’t think of null as “nothing” but as “it could be anything”. Therefore, you cannot check for null values by using

[Completed On]=0

as criteria, neither by using

[Completed On]=Null

To test for nulls, you must use

[Completed On] Is Null

“Is Null” is a fixed expression (to test for non-null values, you would use another fixed expression “Is Not Null”). So try

strCriteria = “[Completed On] Is Null”

Reply | Quote