Hi. I'm relatively new to Access , I have zero experience with VBA and I am stuck (and have been for days). Summary: I have multiple queries based on a form with the parameters between [StartDate} and [EndDate}, and also [CustomerID]. I have my Payments Query [q_Payments], which is entirely based on my Payments table, and has all my Payment Table fields (PaymentID, PaymentDate, PaymentDescription and PaymentAmount) and it is based on the date range parameters from the form. Everything works great.
My issue: There are occasions that there will be no payments during the specified period of time., in fact this is my normal. However, I need to still be able to do a DSum and bring a zero to a report for further calculations. I have tried Nz, Iif , IsNul, etc. and I can't get anything to work. I've tried with the DSum starting the formula, with Nz starting the formula, etc, and I have had no luck in getting something that is not a null value. My latest failed effort is Expr1: DSum(Nz("[PaymentAmount]",0),"[q_Payments]").
On other queries where I expected null values I was able to have this formula work (dsumTest: Format(Nz(DSum("[Test]","[q_Main_Query]"),("0")),"Fixed") and so I was able to bring this DSum to a report, but there was other information retrieved for the query in other fields, like the ID field.
The Payments Query is used to generate a subreport, and if there is no information from the query the subreport will not be printed. (This is exactly what I want to happen, I just wanted to demonstrate the type of null I am dealing with.)
I've spent countless hours trying to find the answer, and I keep hearing that Nz will do the trick, but it doesn't, not when there is no information whatsoever retrieved from the query. Can someone please help?
Thanks.
My issue: There are occasions that there will be no payments during the specified period of time., in fact this is my normal. However, I need to still be able to do a DSum and bring a zero to a report for further calculations. I have tried Nz, Iif , IsNul, etc. and I can't get anything to work. I've tried with the DSum starting the formula, with Nz starting the formula, etc, and I have had no luck in getting something that is not a null value. My latest failed effort is Expr1: DSum(Nz("[PaymentAmount]",0),"[q_Payments]").
On other queries where I expected null values I was able to have this formula work (dsumTest: Format(Nz(DSum("[Test]","[q_Main_Query]"),("0")),"Fixed") and so I was able to bring this DSum to a report, but there was other information retrieved for the query in other fields, like the ID field.
The Payments Query is used to generate a subreport, and if there is no information from the query the subreport will not be printed. (This is exactly what I want to happen, I just wanted to demonstrate the type of null I am dealing with.)
I've spent countless hours trying to find the answer, and I keep hearing that Nz will do the trick, but it doesn't, not when there is no information whatsoever retrieved from the query. Can someone please help?
Thanks.
Last edited: