Blanks, Nulls, "", and what else?

[dawnd3]

Please refresh my memory. When I am searching/filtering for blanks in Access and there are still blanks showing what other type of blank can I search for besides NULL and "" (empty string)? (and I am certain they don't have a space in them, I checked for those too.)

Thanks

Dawn

 

[Andrzejek]

If you use recordsets, you may do:

If Len(Trim(rst.Fields("YourField").Value & "")) = 0 Then
    You havew an empty field
End If

So what I did was:
Added an empty string [tt](& "")[/tt] to the field in case the field is a NULL, Trim'ed it in case is has a Space or Spaces, and check for the Len of the Field. If it is zero, it is empty because of the space(s) or it is NULL

Have fun.

---- Andy

 

[dhookom]

I don't generally allow zero-length strings in my data. If you find you are not getting the desired results it may because you have a carriage return/line feed that is obscuring characters. Did you try adjust the height of the rows in the query?

If I need to be absolutely sure I check for:
Len(Trim([FieldName]&""))=0



Duane
Hook'D on Access
MS Access MVP

 

[MajP]

The provided solution would get 99.9% of the "blank" fields. However, if like Duane says you have special characters it will not. This happens often from an import. A blank field that has nothing but carriage return or tab or other special characters would still appear. Trim will only remove space characters and not other special characters. If the above solution does not solve everything then you can run a query to remove all special characters.