473,325 Members | 2,860 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Query question

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!!!
Attached Files
File Type: zip Database10.zip (42.6 KB, 40 views)
Oct 1 '10 #1
9 1238
code green
1,726 Expert 1GB
Use
Expand|Select|Wrap|Line Numbers
  1. WHERE [field] IS NOT NULL 
in the SQL
Oct 1 '10 #2
Ok....pretend I'm not that good with Access...because I'm really not....is that in the query itself, or in the form for the pop up window?
Oct 1 '10 #3
code green
1,726 Expert 1GB
That is in the query itself.
I can't help with Access forms
Oct 1 '10 #4
Okay, this is what it looks like, but it still does not work:

SELECT Payments.Grant_Name, Sum(Payments.Payment_Amount) AS SumOfPayment_Amount
FROM Payments
WHERE [Payment_Amount] Is Not Null
GROUP BY Payments.Grant_Name
HAVING (((Payments.Grant_Name)="State"));


Where am I going wrong?
Oct 1 '10 #5
code green
1,726 Expert 1GB
The query looks OK to me.
What do you mean by does not work?
Oct 1 '10 #6
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.
Oct 1 '10 #7
code green
1,726 Expert 1GB
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
Expand|Select|Wrap|Line Numbers
  1. SELECT COALESCE(Payments.Grant_Name,""),Sum(Payments.Payment_Amount) AS SumOfPayment_Amount
  2.  
So if the name is NULL it will replace it with an empty string
Oct 1 '10 #8
Error "Undefined function "Coalesce" in expression.
Oct 1 '10 #9
code green
1,726 Expert 1GB
Sorry about that, COALESCE is not supported by Jet.
I think it should be
Expand|Select|Wrap|Line Numbers
  1. IIF(IsNull([Payments.Grant_Name]),"",Payments.Grant_Name)
I have to sign off now so hopefully somebody else may help
Oct 1 '10 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

16
by: cover | last post by:
I have a form with drop down menus to query for name, month, and year to capture activity accordingly by an individual for a given month and given year. But I'd like to also be able to query ALL...
4
by: Spark | last post by:
Hi, Situation: Need a query to return number of ticket records by month of open in a log table where the ticket open record is older than 24 hours then the ticket pending or ticket closed...
3
by: MX1 | last post by:
Here's a simple query question. I have tables. One is an order table and one is an order detail table. tOrder tOrderDetail The tOrder table contains basic info like customer name, date, a...
1
by: Jaycee66 | last post by:
Access 2000 "Between" query question: I am relatively new to MS Access and everytime I think I have a basic concept of the program it pokes me in the eye with something I think would be simple to...
2
by: Erica | last post by:
Hi All, I've searched the usenet/web high and low and cant seem to find any guidelines for the query i'm trying to write. I have made a database that contains all my invoices. Invoices that...
7
by: Scott Frankel | last post by:
Still too new to SQL to have run across this yet ... How does one return the latest row from a table, given multiple entries of varying data? i.e.: given a table that looks like this: color...
3
by: cover | last post by:
I have a table with 50 fields that receive input depending on whether that input came in from a 'shaker' form or a 'conveyor' form. Input from the 'conveyor' form might populate 25 fields while...
10
by: Robert | last post by:
I am an attorney in a non-profit organization and a self-taught programmer. I'm trying to create a client db that will allow me to search for potential conflicts of interest based either on Social...
2
by: Grip | last post by:
Just started teaching myself Access and I've got a newbie question... I have two tables tblPpl and tblOrgs linked by a many-to-one relationship. I'm trying to produce a report that will show a...
3
by: bob laughland | last post by:
Hi All, I have another SQL question, and this one is going to be difficult to explain. I have a table like this rowid name changed 1 a 1 1 ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.