Empty But Not Null

[Nanette]

I ran a query with a Is Not Null function on the PONo on a table that
retrieved many cells that have no data in them. My query is:

SELECT tblLinkPoNAndPartsAndPrN.CabPN,
tblLinkPoNAndPartsAndPrN.PartQtyPerCab, tblLinkPoNAndPartsAndPrN.PONo,
tblLinkPoNAndPartsAndPrN.PartNo, tblLinkPoNAndPartsAndPrN.PRNo,
tblLinkPoNAndPartsAndPrN.APEX
FROM tblLinkPoNAndPartsAndPrN
WHERE (((tblLinkPoNAndPartsAndPrN.PONo) Is Not Null));

I'm thinking that there something in the background of these empty cells?

How can I clean them out?

 

[Ken Snell \(MVP\)]

Might be an empty string (""). Try this query to check:

SELECT tblLinkPoNAndPartsAndPrN.CabPN,
tblLinkPoNAndPartsAndPrN.PartQtyPerCab, tblLinkPoNAndPartsAndPrN.PONo,
tblLinkPoNAndPartsAndPrN.PartNo, tblLinkPoNAndPartsAndPrN.PRNo,
tblLinkPoNAndPartsAndPrN.APEX
FROM tblLinkPoNAndPartsAndPrN
WHERE (((tblLinkPoNAndPartsAndPrN.PONo) = ""));


If the above query returns records, then that is the problem. You could run
an update query to set those records' field value to Null (and then change
the field's properties so that it won't allow empty string values), or you
could use this query to ignore such records:

SELECT tblLinkPoNAndPartsAndPrN.CabPN,
tblLinkPoNAndPartsAndPrN.PartQtyPerCab, tblLinkPoNAndPartsAndPrN.PONo,
tblLinkPoNAndPartsAndPrN.PartNo, tblLinkPoNAndPartsAndPrN.PRNo,
tblLinkPoNAndPartsAndPrN.APEX
FROM tblLinkPoNAndPartsAndPrN
WHERE (((Len(tblLinkPoNAndPartsAndPrN.PONo & "")) > 0));

 

[Nanette]

The empty string was the answer. Thanks for the help!

 

[Jerry Whittle]

It's also possible that someone put in spaces. Something like this should
catch them.

WHERE tblLinkPoNAndPartsAndPrN.PONo Like " *";

 

[Nanette]

What is the syntax for updaing an empty string to a null value?

 

[Nanette]

I figured it out by myself. It's:

UPDATE tblLinkPoNAndPartsAndPrN SET tblLinkPoNAndPartsAndPrN.PONo = IsNull
WHERE (((tblLinkPoNAndPartsAndPrN.PONo)=" "));

 

[John Vinson]

I figured it out by myself. It's:

UPDATE tblLinkPoNAndPartsAndPrN SET tblLinkPoNAndPartsAndPrN.PONo = IsNull
WHERE (((tblLinkPoNAndPartsAndPrN.PONo)=" "));

Sorry, but that is incorrect! IsNull() is a VBA function. I'd use

PONo = Null

instead.

John W. Vinson[MVP]

 

[Per Larsen]

It's also possible that someone put in spaces. Something like this should
catch them.

WHERE tblLinkPoNAndPartsAndPrN.PONo Like " *";

Or:
WHERE Len(Trim(tblLinkPoNAndPartsAndPrN.PONo)) = 0

Regards
PerL

 

[Ken Snell \(MVP\)]

It's also possible that someone put in spaces. Something like this should
catch them.

WHERE tblLinkPoNAndPartsAndPrN.PONo Like " *";

Quite true. I rely too much sometimes on the "auto-deletion" of trailing
spaces that ACCESS does when you enter data via forms.