blank values not null

[Guest]

I have inherited a database with tables linked to a server. In one of the
tables, a field say Fld1 has a few blank values. When I try to filter records
by using filter by form using 'is not null' for Fld1, it outputs even those
records which have a blank value for Fld1. Similarly, if I filter by form for
'Is Null' for Fld1, it gives even those records which have a non-blank value.
The results are the same if I build a query with criteria saying 'is null' in
one case or 'is not null' in the other. How can I make it filter only the
records which have a blank value in Fld1

 

[Van T. Dinh]

If you don't see anything in the Field value, it can be Null or an empty
String or even white spaces.

If you want to trap them all, create a Calculated Field in your Query:

Expr1: Len(Trim([Fld1] & ""))

and in the criteria row, enter the criterion

0

The & "" converts a String or Null to String. Trim removes all white spaces
so if your Field value is Null or has white spaces, the result is an empty
String which has zero length.

 

[Guest]

It works but there are tens of fields in these tables. I would have to
convert each of them to a calculated field to be able to search for blank
values. Is there a way to convert all blanks to nulls; that would also allow
me to Access's built in functionality 'Is Null'

If you don't see anything in the Field value, it can be Null or an empty
String or even white spaces.

If you want to trap them all, create a Calculated Field in your Query:

Expr1: Len(Trim([Fld1] & ""))

and in the criteria row, enter the criterion

0

The & "" converts a String or Null to String. Trim removes all white spaces
so if your Field value is Null or has white spaces, the result is an empty
String which has zero length.

--
HTH
Van T. Dinh
MVP (Access)

I have inherited a database with tables linked to a server. In one of the
tables, a field say Fld1 has a few blank values. When I try to filter
records
by using filter by form using 'is not null' for Fld1, it outputs even
those
records which have a blank value for Fld1. Similarly, if I filter by form
for
'Is Null' for Fld1, it gives even those records which have a non-blank
value.
The results are the same if I build a query with criteria saying 'is null'
in
one case or 'is not null' in the other. How can I make it filter only the
records which have a blank value in Fld1

 

[Van T. Dinh]

The way I posted is the most comprehensive!

If data is imported, you can have one space, 2 or more spaces, tab
character, etc ... as Field values and the expression I posted trap them
all.

Besides, converting from an empty String to Null also requires Calculated
Fields.

If the data is manually entered in Access, you can try the criteria:

"" Or Is Null

for each Field.

 

[Guest]

The data was not manually entered in Access itself but it is coming from a
manually filled in web survey that feeds these tables. These tables are lying
in SQL server and I in my Access database have ODBC connectivity to them
(pardon my technically incorrect language, if any; thats what my
understanding is) It seems that all the blanks in at least all the fields
that I checked could be found by searching for "". That is the number of
results by searching for "" is the same as searching in a calculated field
created by your formula with criteria of 0. I think blanks are all zero
length strings. Do you agree?

 

[Van T. Dinh]

Only you know your data! If you are sure that all blanks from your
DataSource are empty string "", then you can simply search for empty String
without worrying about the white-space characters or Null.