Table cells not empty?

[Phille]

Hi

I have a table wich can posses empty fields, then I have a query that only show rows from the table when that specific field is blank (critweria ="" (also tried =Null)).

Ok! It works fine, untill the field gets a value (obviously) but when I empty the field again it dosen't reappear in the query.

Any ideas? Using XP with Access 2007 Trial

Thanks in advance

[NeoPa]

What is the SQL of your query?
I suspect you may be checking for an empty string then, when you clear it, setting it to Null.
"" =/= Null.
If you post your SQL we can suggest ways to make it work more reliably.

[Phille]

SELECT Cheval.Alias, Cheval.Cheval
FROM Cheval
WHERE (((Cheval.Cheval)=""));

Query1ChevalAlias
N05/Attrape
N05/Porta Marzia
N06/Fleur en Fleur
N06/Palmeira
N06/Porta Marzia
N06/Tashkiyla

ChevalChevalAlias
N05/Maria de la Luz
N07/Fablimixa
N07/Mahima
N07/Morning Rose
N07/Tashkiyla
N06/Bayourida
N06/Flora Fennica
N06/Maria de la Luz
N06/Page Bleue
N05/Attrape
N05/Porta Marzia
N06/Tashkiyla
N06/Fleur en Fleur
N06/Porta Marzia
N06/PalmeiraActs and DeedsN92/AfkazaAdjalaN83/AdjaridaArtillery FireN94/AfkazaAsk for RainN02/RequestingAube d'IrlandeN96/AdjalaAvianeN01/AvernaBayouridaN95/BellaridaBee CharmerN02/BayouridaBella IdaN04/BayouridaFablimixaN92/FabliauFair SusannaN05/Flora FennicaFanny's CoveN79/Honeypot LaneFaster SaraN03/FablimixaFeather BeddingN05/Flowering StoneFire and IceN04/Flowering StoneFlammaN02/Filoli GardensFlax FieldN04/FablimixaFleur en FleurN00/Flowering StoneFlora LinneaN04/Flora FennicaFriars GateN06/FablimixaHerboristeN03/HelvellynKamishaN76/KareezMahimaN02/MacellumMaria de la LuzN96/Light Of HopeMaya de la LuzN03/Maria de la LuzMoon TreeN01/MarsoumehMorning RoseN02/Morning QueenNannettaN98/NotturnaPage BleueN87/Page BlanchePalmeiraN00/PradaPorta MarziaN95/MemsahibPortland StoneN04/Porta MarziaSamsonellaN05/Samata's GemSilvadaN04/Samata's GemTashkiylaN00/TashtiyanaTestN07/Fleur en FleurThe SnowmanN05/TashkiylaThessalonicaN04/Tuneful NineTonaliteN03/TimesTrois RivieresN06/Tuneful NineTuneful NineN98/Tidal TreasureTuning MozartN03/Tuneful NineValley OrchardN00/Valley SpringsValley SpringsN90/Orchid ValeVanoraN06/Valley OrchardVictoria PageN02/Valley SpringsVittoria VetraN04/Valley SpringsWicked FollyN87/Glencoe LightsWindsN02/Wicked Folly

As you can see the table has more empty fields than the query shows.
All the empty fields in the table that dosen't show in the query once had data in them which I afterwards have deleted. So is there some wierd memory that is ghosting around, or what is it?

Thanks

[Phille]

Sorry about that, the table showed up fine when I was writing the post the post. What did go wrong?

[NeoPa]

I'm looking at it now.
I did a Preview Post & it's still a mess so I assume it looked fine in another application before pasting it in here?

[NeoPa]

SELECT Cheval.Alias, Cheval.Cheval
FROM Cheval
WHERE (((Cheval.Cheval)=""));

Query1ChevalAlias
N05/Attrape
N05/Porta Marzia
N06/Fleur en Fleur
N06/Palmeira
N06/Porta Marzia
N06/Tashkiyla

ChevalChevalAlias
N05/Maria de la Luz
N07/Fablimixa
N07/Mahima
N07/Morning Rose
N07/Tashkiyla
N06/Bayourida
N06/Flora Fennica
N06/Maria de la Luz
N06/Page Bleue
N05/Attrape
N05/Porta Marzia
N06/Tashkiyla
N06/Fleur en Fleur
N06/Porta Marzia

N06/PalmeiraActs and DeedsN92/AfkazaAdjalaN83/AdjaridaArtillery FireN94/AfkazaAsk for RainN02/RequestingAube d'IrlandeN96/AdjalaAvianeN01/AvernaBayouridaN95/BellaridaBee CharmerN02/BayouridaBella IdaN04/BayouridaFablimixaN92/FabliauFair SusannaN05/Flora FennicaFanny's CoveN79/Honeypot LaneFaster SaraN03/FablimixaFeather BeddingN05/Flowering StoneFire and IceN04/Flowering StoneFlammaN02/Filoli GardensFlax FieldN04/FablimixaFleur en FleurN00/Flowering StoneFlora LinneaN04/Flora FennicaFriars GateN06/FablimixaHerboristeN03/HelvellynKamishaN76/KareezMahimaN02/MacellumMaria de la LuzN96/Light Of HopeMaya de la LuzN03/Maria de la LuzMoon TreeN01/MarsoumehMorning RoseN02/Morning QueenNannettaN98/NotturnaPage BleueN87/Page BlanchePalmeiraN00/PradaPorta MarziaN95/MemsahibPortland StoneN04/Porta MarziaSamsonellaN05/Samata's GemSilvadaN04/Samata's GemTashkiylaN00/TashtiyanaTestN07/Fleur en FleurThe SnowmanN05/TashkiylaThessalonicaN04/Tuneful NineTonaliteN03/TimesTrois RivieresN06/Tuneful NineTuneful NineN98/Tidal TreasureTuning MozartN03/Tuneful NineValley OrchardN00/Valley SpringsValley SpringsN90/Orchid ValeVanoraN06/Valley OrchardVictoria PageN02/Valley SpringsVittoria VetraN04/Valley SpringsWicked FollyN87/Glencoe LightsWindsN02/Wicked Folly
As you can see the table has more empty fields than the query shows.
All the empty fields in the table that dosen't show in the query once had data in them which I afterwards have deleted. So is there some wierd memory that is ghosting around, or what is it?

Thanks

Looking at this without any table MetaData, I have no idea what you're trying to illustrate :(
What does the long list at the end mean?

Posting Table/Dataset MetaData

Table Name=tblStudent
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
MaxMark; Numeric
MinMark; Numeric

[NeoPa]

SELECT Cheval.Alias, Cheval.Cheval
FROM Cheval
WHERE (((Cheval.Cheval)=""));

Try replacing this SQL with :

SELECT Alias,[Cheval]
FROM Cheval
WHERE IsNull([Cheval]);

Does this do what you want it to?

[Phille]

Hi again

Sorry for not giving a response erlier - my computer died on me.
Now I'm up and running again.

I'm looking at it now.
I did a Preview Post & it's still a mess so I assume it looked fine in another application before pasting it in here?

No it was pasted in the reply box. Try it yourself,copy a few columns from a table and paste them in, they look fine untill you submit it.

Anyways thanks for the help. But I'm getting really confused of how Access interprets the two commands, with criteria ="" I get those that have never had data in them, and with the isnull command I get those cells that are empty but has had a data in them???

Any idea of why?

[NeoPa]

Anyways thanks for the help. But I'm getting really confused of how Access interprets the two commands, with criteria ="" I get those that have never had data in them, and with the isnull command I get those cells that are empty but has had a data in them???

Any idea of why?

Indeed.
Nulls can be the contents of all sorts of fields in Access (As long as they are allowed by the definition) and they mean the field has no data stored therein.
An empty string (""), however, is a valid string (Again as long as they are allowed by the definition) which the field contains. A Null can indicate an empty field of whatever type but "" is an empty string, much like a zero (0) is in a numeric field. Does this help?

[NeoPa]

Hi again

Sorry for not giving a response erlier - my computer died on me.
Now I'm up and running again.

That's not a problem at all.
We respond to your posts, so if you don't post we get a mini-holiday (logically speaking ;))

[NeoPa]

No it was pasted in the reply box. Try it yourself,copy a few columns from a table and paste them in, they look fine untill you submit it.

So this is how it should look then :

Cheval ChevalAlias
 N05    Maria de la Luz
 N07    Fablimixa
 N07    Mahima
 N07    Morning Rose
 N07    Tashkiyla
 N06    Bayourida
 N06    Flora Fennica
 N06    Maria de la Luz
 N06    Page Bleue
 N05    Attrape
 N05    Porta Marzia
 N06    Tashkiyla
 N06    Fleur en Fleur
 N06    Porta Marzia[[/b]    code]
 N06    PalmeiraActs and Deeds
 N92    AfkazaAdjala
 N83    AdjaridaArtillery Fire
 N94    AfkazaAsk for Rain
 N02    RequestingAube d'Irlande
 N96    AdjalaAviane
 N01    AvernaBayourida
 N95    BellaridaBee Charmer
 N02    BayouridaBella Ida
 N04    BayouridaFablimixa
 N92    FabliauFair Susanna
 N05    Flora FennicaFanny's Cove
 N79    Honeypot LaneFaster Sara
 N03    FablimixaFeather Bedding
 N05    Flowering StoneFire and Ice
 N04    Flowering StoneFlamma
 N02    Filoli GardensFlax Field
 N04    FablimixaFleur en Fleur
 N00    Flowering StoneFlora Linnea
 N04    Flora FennicaFriars Gate
 N06    FablimixaHerboriste
 N03    HelvellynKamisha
 N76    KareezMahima
 N02    MacellumMaria de la Luz
 N96    Light Of HopeMaya de la Luz
 N03    Maria de la LuzMoon Tree
 N01    MarsoumehMorning Rose
 N02    Morning QueenNannetta
 N98    NotturnaPage Bleue
 N87    Page BlanchePalmeira
 N00    PradaPorta Marzia
 N95    MemsahibPortland Stone
 N04    Porta MarziaSamsonella
 N05    Samata's GemSilvada
 N04    Samata's GemTashkiyla
 N00    TashtiyanaTest
 N07    Fleur en FleurThe Snowman
 N05    TashkiylaThessalonica
 N04    Tuneful NineTonalite
 N03    TimesTrois Rivieres
 N06    Tuneful NineTuneful Nine
 N98    Tidal TreasureTuning Mozart
 N03    Tuneful NineValley Orchard
 N00    Valley SpringsValley Springs
 N90    Orchid ValeVanora
 N06    Valley OrchardVictoria Page
 N02    Valley SpringsVittoria Vetra
 N04    Valley SpringsWicked Folly
 N87    Glencoe LightsWinds
 N02    Wicked Folly

[Phille]

Hmm

If I understand it, who are you kidding? Getting there - I think : )

Ok, so wheres the

 
 SELECT Alias,[Cheval]
FROM Cheval
WHERE HumanEyeIsNull([Cheval]);