Empty But Not Null

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nanette - 30 Dec 2006 19:40 GMT

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?

Reply to this Message

Ken Snell (MVP) - 30 Dec 2006 20:25 GMT

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));

Signature

Ken Snell
<MS ACCESS MVP>

>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:
[quoted text clipped - 9 lines]
>
> How can I clean them out?

Reply to this Message

Nanette - 30 Dec 2006 22:34 GMT

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

> Might be an empty string (""). Try this query to check:
>
[quoted text clipped - 30 lines]
> >
> > How can I clean them out?

Reply to this Message

Jerry Whittle - 30 Dec 2006 22:36 GMT

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

WHERE tblLinkPoNAndPartsAndPrN.PONo Like " *";

Signature

Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> Might be an empty string (""). Try this query to check:
>
[quoted text clipped - 30 lines]
> >
> > How can I clean them out?

Reply to this Message

Per Larsen - 31 Dec 2006 00:28 GMT

On 30.12.2006 23:36, Jerry Whittle typed the following::

> 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

Reply to this Message

Ken Snell (MVP) - 31 Dec 2006 00:34 GMT

> 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.

Signature

Ken Snell
<MS ACCESS MVP>

>> Might be an empty string (""). Try this query to check:
>>
[quoted text clipped - 34 lines]
>> >
>> > How can I clean them out?

Reply to this Message

Nanette - 30 Dec 2006 22:45 GMT

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

> Might be an empty string (""). Try this query to check:
>
[quoted text clipped - 30 lines]
> >
> > How can I clean them out?

Reply to this Message

Nanette - 30 Dec 2006 23:06 GMT

I figured it out by myself. It's:

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

> What is the syntax for updaing an empty string to a null value?
>
[quoted text clipped - 32 lines]
> > >
> > > How can I clean them out?

Reply to this Message

John Vinson - 30 Dec 2006 23:17 GMT

>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]

Reply to this Message