Query Not Showing Desired Results - HELP

[yus786]

Hi

I have a dead simple query. Here it is in SQL View

SELECT Fees.FeeID, Fees.StudentID, Fees.Fees, Fees.DateEntered, Fees.CollectedBy
FROM Fees
WHERE (((Fees.Fees)=0));

Basically i want to see those records where nothing has been entered for a specific period - i.e. value of 0

But my query is not reporting any of them.

If i went into the table it self and physically entered a ZERO - then it WOULD show in my query but that's defeating my object of finding those students who haven't paid

Any idea's?

 

[Alc]

Is the Fees field actually storing a zero, or is it blank?
If it's blank, you could try

SELECT Fees.FeeID, Fees.StudentID, Fees.Fees, Fees.DateEntered, Fees.CollectedBy
FROM Fees
WHERE IsNull(Fees.Fees);

 

[yus786]

Is the Fees field actually storing a zero, or is it blank?
If it's blank, you could try

SELECT Fees.FeeID, Fees.StudentID, Fees.Fees, Fees.DateEntered, Fees.CollectedBy
FROM Fees
WHERE IsNull(Fees.Fees);

The fees field is setup as currency - if you go into the form - it shows a £0.00 as default.

I have tried the NULL and it didn't work

I also took the currency status of the field and made it into a number - this still didn't work

Soooo frustrating!!!

 

[Alc]

What does the following return as the lowest fee?

SELECT Fees.FeeID, Fees.StudentID, Fees.Fees, Fees.DateEntered, Fees.CollectedBy, Fees.Fees
FROM Fees
ORDER BY Fees.Fees ASC;

 

[khawar]

I think you are trying to get the records in the query result which are not in table
is this the case

 

[yus786]

I think you are trying to get the records in the query result which are not in table
is this the case

yes

Any field which has not been completed (nOT paid for so blank) - i want to qry in

Can this be done?

 

[yus786]

What does the following return as the lowest fee?

SELECT Fees.FeeID, Fees.StudentID, Fees.Fees, Fees.DateEntered, Fees.CollectedBy, Fees.Fees
FROM Fees
ORDER BY Fees.Fees ASC;

just the ones with values against them

 

[Alc]

just the ones with values against them

That query should return all records from the table, as no filter criteria is used at all.

Are there definitely records matching what you want?

 

[yus786]

That query should return all records from the table, as no filter criteria is used at all.

Are there definitely records matching what you want?

Yes. Like i said if i go into to record and just tab over or enter a ZERO - it reports it via the qry

 

[FoFa]

Have you tried this?
SELECT Fees.FeeID, Fees.StudentID, Fees.Fees, Fees.DateEntered, Fees.CollectedBy
FROM Fees
ORDER BY NZ(Fees.Fees,0) ASC;

Which should return the same number rows as this returns as a count:
SELECT COUNT(Fees.FeeID) FROM Fees

Of course have the column and table name the same might be confusing it, have not seen that, but then again, I don't do that (must be a reason, maybe long forgotten).

 

[yus786]

Have you tried this?
SELECT Fees.FeeID, Fees.StudentID, Fees.Fees, Fees.DateEntered, Fees.CollectedBy
FROM Fees
ORDER BY NZ(Fees.Fees,0) ASC;

Which should return the same number rows as this returns as a count:
SELECT COUNT(Fees.FeeID) FROM Fees

Of course have the column and table name the same might be confusing it, have not seen that, but then again, I don't do that (must be a reason, maybe long forgotten).

Tried this and it didn't work.

Also renamed the field from fees to feesvalue - this still didn't work.

I populated the table with ZERO's and then ran the qry - this showed all the zero's. Once i removed the zero's from the table- the qry was blank

help

 

[FoFa]

maybe you should set the default (at the table level) on that column to zero. I usually do that on numeric columns. But it should still show them using that any of that code given you. Strange, really, strange. Let me ask this, when you said you removed the zeros, what did you remove them too (what value)? Did you do it through a query, or through open table?

 

[yus786]

maybe you should set the default (at the table level) on that column to zero. I usually do that on numeric columns. But it should still show them using that any of that code given you. Strange, really, strange. Let me ask this, when you said you removed the zeros, what did you remove them too (what value)? Did you do it through a query, or through open table?

tried that with no joy

I removed them via open table

I think the issue here is i am trying to get the records in the query result which are not in table

My DB has 500 students.

I have entered fees for 10 of them

By running the above query - i expect to see 490 students of the result but as the table itself only has 10 entries (i.e. the 10 i entered) - HOW do i get to report the 490 who haven't paid

 

[FoFa]

Sounds like the column is not null, nor is it zero.
What is the datatype of that column?

 

[yus786]

Sounds like the column is not null, nor is it zero.
What is the datatype of that column?

i've tried Number and Currency

 

[FoFa]

Does this table (fees) have 500 rows in it?
Or does it just have the 10 you entered?

 

[yus786]

Does this table (fees) have 500 rows in it?
Or does it just have the 10 you entered?

Just the 10 i've entered

 

[MrQ]

Query Table fees and your table with all sudent ids, linked by student id and show those records where fees.studentid = null.

 

[khawar]

Upload your sample database if possible

 

[yus786]

Upload your sample database if possible

Ok - i have attached a stripped down version of my DB.

Open the forms Student and enter fees into the value.

If you enter a ZERO - this will show in the qry, If you don't enter anything - nothing will show in the qry.

This DB has 10 names. If i don't enter ANYTHING - i expect the qry to show me the 10 names.

Thanks again