Blank fields not recognized as blank

[connie]

Hi,

I have a very large database that was created from various sources, some of which were done by very infrequent Excel users who did who-knows-what to the data.

There are some records where a field is blank, and for some reason Access is not recognizing those fields as blank. I realized this when I had combo boxes on a form and it was providing multiple blanks in the dropdown - which I was able to work around (thanks to a savvy forum user here!), but what I now want to do is fix these fields in the table itself.

I narrowed it down to which records have the issues by creating a query field by field asking to return Is Not Null values, and then after sorting the returned records A to Z found the fields that were blank but still being returned as Not Null. I went into those records in the table and tried deleting, clearing these blanks, but they are really blank and it didn't help, they're still not being recognized as blank. What can I do?

 

[Kryst51]

Hi,

I have a very large database that was created from various sources, some of which were done by very infrequent Excel users who did who-knows-what to the data.

There are some records where a field is blank, and for some reason Access is not recognizing those fields as blank. I realized this when I had combo boxes on a form and it was providing multiple blanks in the dropdown - which I was able to work around (thanks to a savvy forum user here!), but what I now want to do is fix these fields in the table itself.

I narrowed it down to which records have the issues by creating a query field by field asking to return Is Not Null values, and then after sorting the returned records A to Z found the fields that were blank but still being returned as Not Null. I went into those records in the table and tried deleting, clearing these blanks, but they are really blank and it didn't help, they're still not being recognized as blank. What can I do?

This may seem like a silly question, but what is the data type of the field?

I think that if it is text you need to account for null and empty string, if it is a number, then you need to make it not equal to zero. Those little things can sometimes make a query act wierd.

 

[namliam]

A text field also will be "seen" as empty if it contains spaces, excel users sometimes use a space to "delete" data that was previously there...

So if you are looking for null or empty string make sure to "trim" the string to make sure it is "" instead of " " or multi spaced.

 

[connie]

All of the fields doing that are text fields. I did go into some of these records and select the cell, then hit delete...even put my cursor in there and hit delete many times to make sure there was nothing else in there.

The code I'm using for the form combo box is under Row Source as

SELECT DISTINCT tblEquipmentDatabase.Dept_Name
FROM tblEquipmentDatabase
GROUP BY tblEquipmentDatabase.Dept_Name
HAVING [dept_name] <> NULL

This removed the multiple blanks but there is still a single blank selection available. How would I rewrite that, then? And is there a way I could maybe do an Update Query to fix these records where the blanks aren't seen as blank?

Thx!

 

[Galaxiom]

HAVING [dept_name] <> NULL and [dept_name] <>""
"" is the empty string which is different from null

 

[gemma-the-husky]

try a few queries just for the problem field

visually try as criteria

a) isnull - that will give you the nulls
b) "" that will give you zero length strings

to find all strings consisting of spaces try
trim(fieldname) in the column header then
"" in the criteria row.

then you can do an update query to set them to some standard value, and maybe set a default value ti prevent the nulls happening again

 

[namliam]

HAVING [dept_name] <> NULL

This is invalid syntax, if you are looking for NULL values you have to use IS...

IS NULL
IS NOT NULL

Are the valid ways for working with null, NO other way barring the converting of null to a empty string "" using the NZ function

Thus proper syntax would be
HAVING [dept_name] IS NOT NULL and trim([dept_name]) <>""

Or...
HAVING trim(nz([dept_name],"")) <> ""

The first one is actuall the better, more structured, more complete, "good practice" way of doing it (IMHO)

 

[Galaxiom]

I can't believe I actually cut and pasted <> NULL without noticing, even if I was posting about the empty string.

I can't even blame Error#: C2H5OH as I was about to leave for work. I must be punished.

Public Sub WriteLines()
 
Dim n As Integer
Dim Lines As String
 
     Lines = "I must carefully read what I post."
 
     For n = 1 To 100
          Debug.Print Lines
     Next n
 
End Sub

Things have definitely improved since the 1970s when I could only manage to write lines with three pens at once.

 

[namliam]

WTF is Error#: C2H5OH ???

 

[Atomic Shrimp]

C2H5OH is ethanol (alcohol)

 

[connie]

HAVING [dept_name] <> NULL and [dept_name] <>""
"" is the empty string which is different from null

This worked. Thanks everyone so much!

 

[Galaxiom]

This worked. Thanks everyone so much!

HAVING [dept_name] <> NULL and [dept_name] <>""

Although it might appear to work, technically it doesn't in the strict sense. Access is self correcting and unfortunately encourages the entry of invalid syntax. However this piqued my curiosity so I did some tests. (Access 2007)

Placing <> Null in either the sql or the query designer automatically substitutes Is Not Null in the designer. I believe this is what is actually run. Saving the query and opening it again will show the correct syntax in both views.

It would not be so bad if this behaviour was consistent but it isn't, leading the unsuspecting user into a trap. Access does not self correct = Null. And this expression does not report the syntax error. Nor does it return the nulls.

AFAIK in a text field, for any word but Null the query designer will automatically enclosed it in quotes. Likewise anything else except Like Null or Not Like Null. It appears Access simply has no idea how to deal with = Null, Like Null or Not Like Null except to capitalise the expression. I believe it should report an error when run or saved and this is a bug.

Similarly, the VBA DoCmd.RunSQL method does not report any of these forms as syntax errors in an Update query. But there is no autocorrect in VBA so the <> Null sticks and the expression can be run.

Apparently those records with field <> Null (whatever that really means) are at least a subset of the Is Not Null group and it returns the same records as Is Not Null anyway.

I have not tried the Execute method or a pass through query to SQL server. I'll check this when I am at work on Monday unless someone can try it sooner. I would lay odds sql server rejects it but I wouldn't bet my life on it after what I have just found.

If sql server accepts <> Null and the results are correct, it could be argued that <> Null is quasivalid syntax in Microsoft sql. Maybe Access is autocorrecting the wrong expression.

It would instructive if anyone can come up with a Where clause using <> Null that does not return the same records as Is Not Null. More so to have = Null or Like Null return something.

Once again a wrong answer has revealed more than the right one.

 

[namliam]

<> Null you submit (not tested myself) is after saving promptly re-writen by access to "Is Not Null" this is 'fixing' the faulty syntax, therefor SQL server would never see the bad version and execute the proper one.

That is assuming Access fixes pass through queries just the same as native ones M$ and Logic... those are far and few between (ie. Fixing <> Null, but not fixing = Null)

The problem with fixing this for the average user, you are expecting them to know the difference between "" and Null, yet expect them NOT to know the proper syntax

This worked. Thanks everyone so much!

I am glad for you you think this worked, but this is broken!!! Did you read anything beyond that post??

 

[connie]

Sorry, I'm getting confused and sort of mixed up the two questions I had running on two separate threads. Let me try to clear up:

I was looking to eliminate the multiple blanks in the combo box dropdown as choices, which putting
HAVING [dept_name] <> NULL and [dept_name] <>""
in Design View for the Row Source property did accomplish. If it's not the technically perfect way of doing I wasn't too concerned since the function of the form is programmed through the buttons anyways, and that's where it's told how to recognize Is Null or Is Not Null.

If I'm to be technically correct, this really doesn't need grouping at all but I don't know how to take that out since when I do I get an error for using "HAVING" without grouping...
HAVING [dept_name] IS NOT NULL and trim([dept_name]) <>""

As for the root problem, there are still erroneous records showing up where they don't belong on the output report based on 2 queries triggered by that form. That thread is here and any help on that is also much appreciated. I suppose this should have all been one thread but my eyeballs were so sore last week I was seeing double

 

[namliam]

Having is just another Where clause for the grouping, simply move your Having to a where and get rid of the Grouping...

*ugh* for starting multiple threads !

 

[connie]

Having is just another Where clause for the grouping, simply move your Having to a where and get rid of the Grouping...

*ugh* for starting multiple threads !

Thanks! Sorry about the multiple threads...it started out as 2 separate issues (I thought) - the combo box having extra blanks in the dropdown vs. my queries pulling random erroneous records (empty strings, I guess). But then I got all intertwined. *sheepishly sticks out wrist for ruler*

 

[namliam]

*sheepishly sticks out wrist for ruler*

Go write "I shall not start duplicate threads" 10.000 times BY HAND no coding!

Then be nice to the person next to you for a week, a person that you REALLY hate...

Then the next week walk his dog for him AND carpool with him to work

The root problem of both threads though is a basic misunderstanding of how NULL works and how it works both inside and outside of SQL.