TRIM Function

[Guest]

I am having a problem in querying what records have null value in a table. I
know there are 80 Null out of 100 rec.
In a query I say, Where Name is Null, I get 60 null rec. If I try Where Name
is "", I get 70 rec.
1. Why am I not receiving the other 10 records?
2. If I run TRIM function will it help? If yes how can I write the query
using TRIM?

 

[OfficeDev18 via AccessMonster.com]

The Nz() function fits beautifully here. To query for no-value, your query
would look like:

SELECT * FROM YourTable WHERE Nz(SomeField," ")=" "

If the possibly-null field is numeric or date, you must make a tweak as
follows:

SELECT * FROM YourTable WHERE Nz(SomeField,0)=0

See the Nz() function in the Help for details. This will return all records
where SomeField is null, equals "", or equals " ".

HTH

 

[George Nicholson]

Null and an empty string (aka "" are not the same thing. A space (aka " ")
is something else again.

Try: Where trim(nz([Name],"")) = "" or (my preference) Where
Len(trim(nz([Name],""))) = 0

The nz (null-to-zero) function will cause Null values to be evaluated as if
they were "".
Similarly, the trim function will cause any " " entries to evaluate as "".

(Using Len or Trim on [Name] will generate an error if Name is Null unless
you use nz() first.)

HTH

 

[Gary Walter]

PMFBI

my favorite test for "blank" field:

Len(Trim([somefield] & "")) = 0

Apologies again,

gary

Null and an empty string (aka "" are not the same thing. A space (aka " ")
is something else again.

Try: Where trim(nz([Name],"")) = "" or (my preference) Where
Len(trim(nz([Name],""))) = 0

The nz (null-to-zero) function will cause Null values to be evaluated as
if they were "".
Similarly, the trim function will cause any " " entries to evaluate as "".

(Using Len or Trim on [Name] will generate an error if Name is Null unless
you use nz() first.)

HTH