Null on DSum (1 Viewer)

[TB11]

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.

 

[plog]

Look closely at the one that works and the one that doesn't. Order of formulas is important--put the NZ around the DSum.

If that doesn't get it, can you post a sample of your database?

 

[Isaac]

Why not just create a query with a column like this, and base the query off of q_Payments source
PaymentAmount: Nz([PaymentAmount],0)

Then do your Dsum on that new query's column PaymentAmount.

 

[TB11]

Plog: I've tried that. I even copied the working DSum formula and changed the names. Didn't work, and I tried it multiple times.

Issaac: I've tried that too, doesn't work.

In each instance, nothing is returned except the column header. Frustrating to say the least.

 

[June7]

I did DSum() test with a query that has no records retrieved. The Nz() works.

Suggest you provide db for analysis.

 

[TB11]

I've attached my database entitled ProblemChild. I copied the tables, query and form from my database that is at issue and removed my info and replaced it with made up info. The form that controls the parameters is f_Start_End_Date. The query q_Main_Query drives all of the reports and subreports. The query q_Main_1_Time_Dsum has the Nz DSum formula that works.

The query q_Main_3_Payments_Dsum is the one that will not (still - even on the "new" database) return a 0, or "none", or anything else for the Nz Dsum value. I left the "format" in the formula, but I've taken it out and still no luck.

Thanks!

 

[Isaac]

See attached .. I just added a PaymentAmountNZ to the underlying query, then dsum() that in the secondary query. Selected Johnson on the cbo, and the secondary query seems to produce a result.
?

 

[June7]

q_Main2_Payments has no records then use it to build q_Main3_Payments_Dsum query so of course the second query will not return records. Has nothing to do with a DSum() calc in that second query. Why not just an aggregate query instead of DSum()? Use domain aggregates on report.

Build a report and use its Sorting & Grouping with aggregate calcs. Apply filter criteria to report.

 

[Isaac]

q_Main2_Payments has no records then use it to build q_Main3_Payments_Dsum query so of course the second query will not return records. Has nothing to do with a DSum() calc in that second query.

You have to select Johnson from cbo first ...

 

[June7]

@Isaac, my comment was meant for OP based on their db, not your revision.

Controls used for input of filter criteria should be UNBOUND, otherwise data is changed.

Exactly what is purpose of Dates_Begin_End_tbl? None of the queries even use this table.

 

[Isaac]

Yes my comment was referring to the database they posted..

 

[June7]

Not sure that makes sense. If they want to find Doe records why would they enter Johnson? If query is empty because of no data for Doe, they still want to see 0.

@TB11, do this test in VBA immediate window:
?Nz(DSum("PaymentAmount","q_Main_2_Payments"),0)

 

[Isaac]

Not sure that makes sense. If they want to find Doe records why would they enter Johnson?

Because Johnson was the record in the payments table..

 

[TB11]

I use the form to generate reports that are date range specific and also based on single client. I didn't include these reports in the upload as they are irrelevant since I can't get the Nz Dsum to work, and therefore errors come to the report. The report is essentially a recap of the time less payments.

@Isaac I appreciate the database uploaded. The Nz Dsum only generates when the form's combo box has Johnson (client) selected. On Doe and Smith (clients) no DSum or other value generates. This is why I am stuck. I need to have a value for Doe and Smith to include in the recap report.

@June7 I apologize. I have no clue where to go for VBA immediate window. Obviously I need to learn VBA.

 

[June7]

@Isaac, if desire is to limit search to only clients that have records in payments then they should use a combobox that lists only those clients. I revised my previous post after you read it. I am still wondering why this form and controls are BOUND.

@TB11, easy to get started with VBA. Click the VisualBasic button on DatabaseTools tab. Most likely the immediate window will be active by default at bottom.

If you need to include all clients on report regardless of data, that will get complicated. This is a fairly common topic.

 

[TB11]

@June7 The combo box is bound as I need to track when the client gets the recap and what date range is involved. The recap report is only generated for the client selected via combo box, and just for date range specified.

Thanks for the tip on where to start with VBA. Will check it out tomorrow when I'm back at my computer.

 

[June7]

Then don't you need to move to new record row when entering client and dates so you don't change existing record? Perhaps form should be set to only allow new record entry and not display existing?

That is only one way to open the VBA editor. Review http://baldyweb.com/ and look at items under VBA section.

 

[TB11]

I wanted to thank everyone for your help. I ended up with a work around for the issue.

@June7 thanks for the nudge to get me started with VBA. I've started learning it and can already see how essential it is.

@Isaac your help got me thinking about another issue I had, which is now resolved.