Blank text fields,

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query which pulls account names, sometimes the field is blank.
I want to only pull the records which have a text in the field.
I have tired Is Not Null but it brings in the blank fields, and I have tired
""
and it eleminates all the account names. I am putting this in the criteria
of the query.
Is there a way to do this.
Thanks for any help
Roger
 
Create a Calculated Field in your Query:

NameLen: Len(Trim([AcctName]) & "")

Set the criteria > 0 in the criteria row.

This criteria will eliminate all Null values and white spaces.

BTW, you probably need to check AllowZeroLength in the Table Field as well.
See Access Help for the interworking of the Field Properties "Required" and
"AllowZeroLength".
 
Hi
I must be doing something wrong.
I get the number of charaters in the account name, not the account name
Thanks

Van T. Dinh said:
Create a Calculated Field in your Query:

NameLen: Len(Trim([AcctName]) & "")

Set the criteria > 0 in the criteria row.

This criteria will eliminate all Null values and white spaces.

BTW, you probably need to check AllowZeroLength in the Table Field as well.
See Access Help for the interworking of the Field Properties "Required" and
"AllowZeroLength".

--
HTH
Van T. Dinh
MVP (Access)



XeniaEagle said:
I have a query which pulls account names, sometimes the field is blank.
I want to only pull the records which have a text in the field.
I have tired Is Not Null but it brings in the blank fields, and I have tired
""
and it eleminates all the account names. I am putting this in the criteria
of the query.
Is there a way to do this.
Thanks for any help
Roger
Click to expand...
Click to expand...
 
That's the idea.

Leave AcctName column *alone*. I wrote in my previous post to *create a
(new) Calculated Column:

NameLen: Len(Trim([AcctName]) & "")

Uncheck the "Show" option if you don't want to see the length. Place the
criteria:
0
Click to expand...

in this Calculated Field / Column.

The criteria in this Calculated Column will select only Records that has
non-blank Account Names.

--
HTH
Van T. Dinh
MVP (Access)
 
Back
Top