Form calcualted control using calculations from a query.

[MackMan]

Now, please don't laugh...

I'm trying to perform what will no doubt to many, be the simplest of Access functions... but to me - well, it's driving me crazy, and I've been just about every conceivable way through the problem, with no luck - despite trolling these forums for hours.

All I'm after is, to sum in all records "where" reconcileDate (on the sub=form) = today in both the credit and debit fields and minus one from the other (image attached)

As you can see, I have a simple form and sub-form. On the sub-form I have a query based calculation that returns the following...
"if ReconcileDate (on sub-form) = today, then show Debit value in yndebit" textbox
Likewise with the credit box, and all works perfectly fine. so far so good...

However, I can't seem to sum these two boxes. Because the circled textboxes are query based, I've used the query as the control source (and not the text boxes) and all I get is an #ERROR? when placed in the form footer. I've tried to do this using the textboxes as the control source, and still nothing. I've tried to add a calculated control on the form itself, and still #ERROR? or #NAME? despite knowing exactly how to reference subforms within forms (this I've become quite an expert at as I simply use query design to make sure I have the correct path and control)anyway... I'm not at a loss, and starting to think it's time to give up

Like I say, an incredibly simple function to sum for some.

Any help to point me in the right direction would be greatly appreciated.

 

[Alfisdad]

If I understand you correctly, for a particular record on the subform, yndebit and yncredit will be set to the debit and credit values respectively if the reconcile date on the record is today or will be blank otherwise and the yndebit and yncredit values are calculated in the query that is the recordsource for the subform?

I wonder if the problem is that the yndebit and yncredit values for records where the reconcile date is not today are NULL and it is complaining about trying to SUM values that include NULLs.

Are yndebit and yncredit visible fields on the form? If not, you could change your query such that these values are set to 0 for records where the reconcile date is not today. Alternatively, if they must be NULL for display purposes, have another value on the query which holds the difference between the debit and the credit figure for that record, e.g.
iif(ReconcileDate = Date(), Debit - Credit, 0) and then sum the invisible field in the form footer.

Worth a try?

 

[MackMan]

Hi Alf

If I understand you correctly, for a particular record on the subform, yndebit and yncredit will be set to the debit and credit values respectively if the reconcile date on the record is today or will be blank otherwise and the yndebit and yncredit values are calculated in the query that is the recordsource for the subform?

You're absolutely right. That's exactly what I'm doing.

Using the query for the subform, I'm pulling all data where there is no date in the ReconcileDate (hence pulling all transactions that have yet to be reconciled) and using the subform to update as I do so.

I'm using the =nz(sum([subform controllocation]),0) (I've even tried using the query itself)) but... after I've updated the YN field to pull todays date there will be no null values to sum up. In the past when reconciling statements, there have been differences, and i'm trying to sum the credit and debit value (after reconciliation) to work out the statement difference, and these are/should be equal. If so, then the reconciled amount is equal to the statement amount, and reconciliation can be completed. I've got a button to "complete" and if there's a difference between the two, then an event on the button will fire a msgbox to say it's incorrect, and must go back to check values.

Seems simple enough, but it's giving me headache!!

 

[Alfisdad]

Would you be able to post the SQL of the query and the control source of the field in the footer of the subform which is giving the #ERROR?

 

[MackMan]

I've tried the following - based on query ; qryRoncileSub

unbound textbox name is [TotalYNCredit] in the footer.

=DLookUp(Sum([yncredit]),[qryReconcileSub]) .. #NAME? (I'm missing something here)


OR


=Sum(Nz([Forms]![frmReconcile]![frmReconcileSub].[Form]![yndebitqry],0)) .. #ERROR? (more than one calculated control??)

I've trolled the internet for over a 12 hours now, and I'm still no closer to what I would class as "the" most simplest of calculations.:banghead:

 

[JHB]

Post a stripped version of your database with some sample data, zip it.

 

[Alfisdad]

In the form footer of the subform, try changing the controlsource of [TotalYNCredit] to =SUM(Nz([yncredit],0). I assume you have a similar control called [TotalYNDebit] in the footer? Set the control source of that to =SUM(Nz([yndebit],0))

 

[MackMan]

Now, excuse the crudity of the forms and so on, it's no where near finished.

When it opens, you'll go straight to the form that's giving me trouble.
Other data is only example.


The Statement Difference is already loaded, and in this example is, say, -£2,000
Once all tick boxes on the sub form are ticked, the total should also be -£2,000 (i.e. as if it was being checked off an invoice)
I will then write some VBA to proceed to finalise, or return to check if the differences are not the same. (At this time, the -£2,000 means nothing.. I'm just trying to sum the sub form)

Only problem is, I can't get to sum the "ybdebitqry" and "yncreditqry" on the sub form once the tick box has been checked.

 

[Galaxiom]

=DLookUp(Sum([yncredit]),[qryReconcileSub]) .. #NAME? (I'm missing something here)

Yes. The arguments of the DLookup are strings. At best you are passing a number as the first argument.

I have not looked at what you really need but this looks like what you were trying to do:

=DSum("[yncredit]","[qryReconcileSub]")

=Sum(Nz([Forms]![frmReconcile]![frmReconcileSub].[Form]![yndebitqry],0)) .. #ERROR? (more than one calculated control??)

Controls cannot be summed. The Sum function applies to field in the form's recordset.

Where you want to display the results of a subform summed field on a main form, the Sum is done in a control on the subform then a main form control uses a reference to that control.

 

[MackMan]

Well, the Dsum certainly got rid of both the #ERROR? and #NAME? handlers.

And it's "almost there"... but I'm looking at summing all [yncreditqry] and yndebitqry] that are valued today only. Eventually these will all be completed fields as time goes on, but looking for sum of each of them fields where the [reconciledate] date = today.

Dsum... Never thought of that one.

 

[Alfisdad]

You don't need to use DSum.

I've had a look at the database you posted and the problem lies in the control source of your form footer fields. You currently have:
=Sum(Nz([yndebitqry],0)) and
=Sum(Nz([yncreditqry],0))

As per my post below you need to have:
=Sum(Nz([yndebit],0)) and
=Sum(Nz([yncredit],0))

yndebitqry/yncreditqry are the names of the fields on the form, you need to be referencing the names of the data columns from the query.

 

[MackMan]

Alfisdad. Many thanks!!!
I knew I was missing something obvious!

Really appreciate your help with this one!

Merry Christmas to you.

 

[Alfisdad]

Just glad to be of help - consider it a Christmas gift from me

Merry Christmas to you too...

 

[MackMan]

I genuinely appreciate it.

There's one slight problem with the Sum function on the datasheet view working one record behind on the updates, but I'll figure it out! .

lol


[UPDATE] all sorted. I just added an Afterupdate even on the subforms checkbox...

Me.Parent.Requery

purrs like a kitten!