Query question

[noclueforid]

I am having an issue with the attached sample database. It tracks payments made on a training contract. The user enters the students name, date paid, payment amount and a grant source.

The grant name field is a drop down list of available grant names. If the user double clicks the box, it will show a pop up window with the grants: amount total paid on them, amount of the grant, and amount left on the grant.

The problem is if a grant name has no payments assigned to it yet, it blanks out the whole pop up grant window. How can I prevent this?

Thanks!!!

[code green]

Use

WHERE [field] IS NOT NULL

in the SQL

[noclueforid]

Ok....pretend I'm not that good with Access...becaus e I'm really not....is that in the query itself, or in the form for the pop up window?

[code green]

That is in the query itself.
I can't help with Access forms

[noclueforid]

Okay, this is what it looks like, but it still does not work:

SELECT Payments.Grant_ Name, Sum(Payments.Pa yment_Amount) AS SumOfPayment_Am ount
FROM Payments
WHERE [Payment_Amount] Is Not Null
GROUP BY Payments.Grant_ Name
HAVING (((Payments.Gra nt_Name)="State "));


Where am I going wrong?

[code green]

The query looks OK to me.
What do you mean by does not work?

[noclueforid]

The form that displays the results of the query is completely blank. If I do not have any payments for "State", its all blank. If I have at least one payment for each type of grant, its ok. But thats not likely to happen every year, so I need it to show even if there are no payments made to it.

[code green]

The problem is if a grant name has no payments assigned to it yet, it blanks out the whole pop up

It is hard to visualise what is happening here.
It seems like the form is showing a null field with the full size of the field. You don't state which field this is.
As a hack try

SELECT COALESCE(Payments.Grant_Name,""),Sum(Payments.Payment_Amount) AS SumOfPayment_Amount

So if the name is NULL it will replace it with an empty string

[noclueforid]

Error "Undefined function "Coalesce" in expression.

[code green]

Sorry about that, COALESCE is not supported by Jet.
I think it should be

IIF(IsNull([Payments.Grant_Name]),"",Payments.Grant_Name)

I have to sign off now so hopefully somebody else may help