Caculation of categorized field's total in form footer

[gykiang]

Hi

I need help to complete one of my forms.

In the attachment, I have 5 “Due” fields in the Form Footer to work on, they are :
[Due30]
[Due60]
[Due90]
[Due120]
[Due150]

In the body of the form, I have key data value of [InvDate], [InvoiceNo] & [INVTOTALSUM] to provide info to those “Due” fields.
The objective of “Due” fields are to find out the sum of [INVTOTALSUM] outstanding in a given period category of 30/60/90/120/150.

Therefore in all the 5 “Due” fields, what expression should I write to achieve the desired total of [INVTOTALSUM]. Example :


Due30: IIf(Forms![frm04Soa]![frm04SoaAccs].Form![StatementDate]-[InvDate] < 30, ………..)

Thanks in advance the help rendered.

Regards
Kiang

[June7]

Try:

Sum(IIf(Date()>=InvDate+30, [INVTOTALSUM], 0))

[gykiang]

Try:

Sum(IIf(Date()>=InvDate+30, [INVTOTALSUM], 0))

Thanks.


Didn't work, it returns with #error.

[June7]

AFAIK, it should. I don't know your db so can't debug.

[gykiang]

I tried again without correcting the expression, it seems to work with Date() which is current date.

I want to replace Date() with [StatementDate] which is in an embedded unbound subform. Below was the correction that yield #error :

Sum(IIf([Forms]![frm04Soa]![frm04SoaAccs].[Form]![StatementDate] =InvDate+30, [INVTOTALSUM], 0))

Experimentally, I tried putting [Forms]![frm04Soa]![frm04SoaAccs].[Form]![StatementDate] in the control source of field [Due30], it returned with "42,516.00" & when I changed format to Date instead Standard, the date is correct then.

Any clue of correcting the expression ?

Thanks !

Regards
Kiang

[June7]

What do you mean by unbound - where does the subform data come from? If it's the one in the image, looks bound to me.

I always name subform container different from the object it holds, like ctrDetails.

Sum(IIf([ctrDetails]![StatementDate] = InvDate + 30, [INVTOTALSUM], 0))

Shouldn't it be: >= ?

[gykiang]

Yes, you are right, my typo error. It should be >=

I'm not sure I'm using the right terminology. What I'm trying to said, the table of the subform has no link to the table of the main form.

[gykiang]

The name of the subform is frm04SoaAccs

So I have changed the expr to

=Sum(IIf([frm04SoaAccs]![StatementDate]>=[InvDate]+30,[INVTOTALSUM],0))

it returns again to #error

[gykiang]

I think it has to do with the format of the field.

When I change the control to

= [frm04SoaAccs]![StatementDate]

it returns with 42,156.00

if I change the format of control to date format, it shows the correct date of [frm04SoaAccs]![StatementDate]

[June7]

So the expression is working? If not, try using CDate() function.

CDate([InvDate])

[gykiang]

I tried :

=Sum(IIf([frm04SoaAccs]![StatementDate]>=CDate([InvDate])+30,[INVTOTALSUM],0))

And

=Sum(IIf(CDate([frm04SoaAccs]![StatementDate])>=CDate([InvDate])+30,[INVTOTALSUM],0))

Both return to #Error

If

= CDate([frm04SoaAccs]![StatementDate])

Or

= CDate([InvDate])

result is correct after changing control format from standard to date format.

[gykiang]

Have just tried :

=[frm04SoaAccs]![StatementDate]-[InvDate]

The result is correct with 151 days.

[June7]

This is resolved?

[gykiang]

This is resolved?

No

Regards
Kiang

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.