Find fields with nothing in them...

GrezMnky
10-18-2004, 03:31 PM
From time to time in select queries in MS Access I look for blank fields by using IS NULL or "" (no space). Sometimes this does not pick all of the "blank" fields.

How do you query for these fields that are not Null or equal to""?

Thanks

cmM
10-18-2004, 04:22 PM
From time to time in select queries in MS Access I look for blank fields by using IS NULL or "" (no space). Sometimes this does not pick all of the "blank" fields.

How do you query for these fields that are not Null or equal to""?

Thanks
If "Null" is your search criteria in your select querry then it should return all Null values.
Maybe you have spaces in those fields that are not returned?

GrezMnky
10-18-2004, 04:24 PM
I seem to remember checking for spaces but not finding any.

Would doing a TRIM function on all cells, besides removing leading and trailing spaces, would it also delete/clean up fields that just have spaces?

NEOLLE
10-19-2004, 07:33 AM
Would doing a TRIM function on all cells, besides removing leading and trailing spaces, would it also delete/clean up fields that just have spaces?
Hi GrezMnky,
I all depends on how and where did you use the Trim$ Function. :)

GrezMnky
10-19-2004, 07:36 AM
How could you use it to change fields with spaces to NULL or ""?

BTW could it be that there is actually a SOFT RETURN or TAB char in the field instead of spaces or NULL?

Demon Cleaner
10-19-2004, 07:39 AM
Try to create a VB select using the ASCII codes of the characters in order to clarify if this happens or not.

NEOLLE
10-19-2004, 07:42 AM
How could you use it to change fields with spaces to NULL or ""?

BTW could it be that there is actually a SOFT RETURN or TAB char in the field instead of spaces or NULL?

If you can not use SQL Statements to query this, null-like fields. Do a full table scan Query (Select * from TableName) and use Loop to read each records and Update these fields.

Well you can assign a default value for your field, just to avoid spaces and nulls.
:)

GrezMnky
10-19-2004, 07:45 AM
True about the default value, but I think the problem arises when you import data from other sources (excel or another db). Data is like a box of chocolates - you never know what you gonna get.

MKoslof
10-19-2004, 06:31 PM
I assume you are querying from a table. What are the defined data types of these fields? Depending on what data type you are working with..the empty or null reference will differ.

GrezMnky
10-20-2004, 06:57 AM
I assume you are querying from a table. What are the defined data types of these fields? Depending on what data type you are working with..the empty or null reference will differ.

Is there a list of null references per data type somewhere?

Text
Memo
Number
Date/Time
Yes/No
Currency
OLE Object
Hyperlink

MKoslof
10-20-2004, 06:42 PM
It also depends if you are setting any default field values for your tables. For instance, in Access an integer field will default to 0. So, you can check if the value of the field is <> 0. Yes/No fields are stored as bitwise components, meaning you can compare against their bit values (0, -1). For dates in Access, if you have empty values you can compare against IS NULL or NOT IS NULL the same applies for standard text fields.

Itsatish
10-21-2004, 04:02 AM
The best option is to check the fields in the table row knowing exactly what it is having.Might be that internal string comparisions are failing.For there is a technical diff between a NULL,"" and " ".
Create a row the values of which you are knowing and then debug and check...

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum