Empty But Not Null

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

Guest

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?
 
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));
 
It's also possible that someone put in spaces. Something like this should
catch them.

WHERE tblLinkPoNAndPartsAndPrN.PONo Like " *";
 
I figured it out by myself. It's:

UPDATE tblLinkPoNAndPartsAndPrN SET tblLinkPoNAndPartsAndPrN.PONo = IsNull
WHERE (((tblLinkPoNAndPartsAndPrN.PONo)=" "));
 
I figured it out by myself. It's:

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

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

PONo = Null

instead.

John W. Vinson[MVP]
 
It's also possible that someone put in spaces. Something like this should
catch them.

WHERE tblLinkPoNAndPartsAndPrN.PONo Like " *";
Click to expand...

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

Regards
PerL
 
Jerry Whittle said:
It's also possible that someone put in spaces. Something like this should
catch them.

WHERE tblLinkPoNAndPartsAndPrN.PONo Like " *";
Click to expand...

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