Criteria for blank fields (query)

[stafire_18]

I have a table that has a column for addresses. there are some addresses in this field & there are no addresses in this field (blank cells) If I just wanted a query to select the fields that contain an address & not a blank what do I put in the query?

 

[Joe4]

You can put "Not Is Null" as the criteria of this field.

 

[stafire_18]

thank you very much!

 

[stafire_18]

thank you very much!

 

[MrAccess97]

No address

stafire_18,

Actually using "Not Is Null" in situations like this will eventually find that person who thinks "deleting" an address is the same as making the field appear blank. In other words, sooner or later that perverbial "spacer" will crop up; yes that person who "blanks" the address by highlighting the entire entry and hitting the spacebar.

A better criteria is to ensure Nulls, zero-length string, etc. is handled so you don't have to revisit the query later and try to figure out why this seemingly "blank" address is now showing up is below.

I always use the Trim$([field_name] & " ") <>"" when equating. This then takes care of the Nulls, zero-length strings, etc. all at once. When used as a field in a select query it also eliminates having to also specify Is Null or Not Is Null in the criteria since it already took care of them.