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

[b]Query1[/b]ChevalAlias
N05/Attrape
N05/Porta Marzia
N06/Fleur en Fleur
N06/Palmeira
N06/Porta Marzia
N06/Tashkiyla

[b]Cheval[/b]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
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)=""));

[b]Query1[/b]ChevalAlias
N05/Attrape
N05/Porta Marzia
N06/Fleur en Fleur
N06/Palmeira
N06/Porta Marzia
N06/Tashkiyla

[b]Cheval[/b]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

N06/PalmeiraActs and DeedsN92/AfkazaAdjalaN83/AdjaridaArtille ry FireN94/AfkazaAsk for RainN02/RequestingAube d'IrlandeN96/AdjalaAvianeN01/AvernaBayourida N95/BellaridaBee CharmerN02/BayouridaBella IdaN04/BayouridaFablim ixaN92/FabliauFair SusannaN05/Flora FennicaFanny's CoveN79/Honeypot LaneFaster SaraN03/FablimixaFeathe r BeddingN05/Flowering StoneFire and IceN04/Flowering StoneFlammaN02/Filoli GardensFlax FieldN04/FablimixaFleur en FleurN00/Flowering StoneFlora LinneaN04/Flora FennicaFriars GateN06/FablimixaHerbor isteN03/HelvellynKamish aN76/KareezMahimaN02/MacellumMaria de la LuzN96/Light Of HopeMaya de la LuzN03/Maria de la LuzMoon TreeN01/MarsoumehMornin g RoseN02/Morning QueenNannettaN9 8/NotturnaPage BleueN87/Page BlanchePalmeira N00/PradaPorta MarziaN95/MemsahibPortlan d StoneN04/Porta MarziaSamsonell aN05/Samata's GemSilvadaN04/Samata's GemTashkiylaN00/TashtiyanaTestN 07/Fleur en FleurThe SnowmanN05/TashkiylaThessa lonicaN04/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

[b]Table Name=tblStudent[/b]
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 :

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

[Phille]

Hmm

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

Ok, so wheres the

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

Jokes aside. So you mean the only way to be sure to get all the data is by combining the ="" and IsNull? Sounds complicated.