Sum giving me an error, asking for a parameter or showing blank

[June7]

The sample did not have the calcs for Null and did not allow shrink.

I always explicitely specify the alternate value for Nz() function, otherwise I believe the value will be empty string and that is not desired for math operation.

Nz(PaymentReceived1st,0)

Are the report records properly filtering?

The issue is still just the sum?

[Ruegen]

The sample did not have the calcs for Null and did not allow shrink.

yes I was wondering if that was an issue

I always explicitely specify the alternate value for Nz() function, otherwise I believe the value will be empty string and that is not desired for math operation.

Nz(PaymentReceived1st,0)

note: when I did this (added the ,0 to the nz()) I get a cannot function error but it still works and lets me save it that way. (I was adding that to the isnull() edit that comment out of history)

Are the report records properly filtering?

The issue is still just the sum?

yes - and they collapse correctly and hide correctly

I made a copy of the company front end and removed the formulas on the text boxes in the detail section so that the only formula would be sum - I still get an error so it is something I must have wrong in the sum regardless of any of the fields.

Also

with

=Sum(IIf([PaymentDepositDateBanked]<>[Forms].[frmCompanyTaxInvoices].[txtDate],0,Nz([PaymentDepositReceived],0))+IIf([PaymentDateBanked1st]<>[Forms].[frmCompanyTaxInvoices]![txtDate],0,Nz([PaymentReceived1st],0))+IIf([PaymentDateBanked2nd]<>[Forms].[frmCom

does it matter when you use . vs ! when typing forms!frmcompanytaxinvoices!txtdate

?

I noticed sometimes you use . .ie forms.frmcompanytaxinvoices!txtdate

[June7]

Sorry, the sample works and should be translatable to other db.

I did not have to use Format on the dates. Maybe that's an issue. Format results in a string value, not a date, although Access and VBA should be able to automatically recognize the string as date if properly formatted and reconcile.

Are you not in U.S.? International date can be problemmatic. Have you seen http://allenbrowne.com/ser-36.html

[Ruegen]

Sorry, the sample works and should be translatable to other db.

I did not have to use Format on the dates. Maybe that's an issue. Format results in a string value, not a date, although Access and VBA should be able to automatically recognize the string as date if properly formatted and reconcile.

Are you not in U.S.? International date can be problemmatic. Have you seen http://allenbrowne.com/ser-36.html

I'm looking for any possibilty - making sure dates work. It filters fine, shrinks fine. It's just the sum

I am only summing the fields and making sure each text box is labelled "txtFieldName" and not their source name just to clear any mismatch confusion but I still get the error.

I have the sample working perfectly the way I want it to as you can see here

https://www.dropbox.com/s/5vbw8mzqiq...atabase3.6.zip

How could I know if it is a date string error?

update: I am reading through Allen's page - (I should fly to Perth and just ask him )

A picture of the table fields - everything seems to be in order.

[Ruegen]

If I use

=Sum(IIf([PaymentDateBanked1st]<>[Forms]![frmCompanyTaxInvoices]![txtDate],0,Nz([PaymentReceived1st],0))+IIf([PaymentDateBanked1st]<>[Forms]![frmCompanyTaxInvoices]![txtDate],0,Nz([PaymentReceived2nd],0))+IIf([PaymentDepositDateBanked]<>[Forms]![frmCompanyTaxInvoices]![txtDate],0,Nz([PaymentDepositReceived],0)))

I get # error

If I remove one and go with

=Sum(IIf([PaymentDateBanked1st]<>[Forms]![frmCompanyTaxInvoices]![txtDate],0,Nz([PaymentReceived1st],0))+IIf([PaymentDateBanked1st]<>[Forms]![frmCompanyTaxInvoices]![txtDate],0,Nz([PaymentReceived2nd],0)))

it works

so there is something wrong with deposit

update:

ok - if I sum any two it works, 3 it doesn't. WHY??

[June7]

You have PaymentDateBanked1st in the expression twice, should one be PaymentDateBanked2nd?

However, doubt that will resolve issue of 3rd term.

The expression with 3 terms is longer than 255 characters but I didn't think that was the limit on expressions.

The . provokes intellisense popup tips in VBA.

Forms!frmCompanyTaxInvoices. will not trigger intellisense but Form_frmCompanyTaxInvoices. does.

The first is used by Access in objects, the second is VBA exclusive. Look at the objects listed in VBA editor and note the prefix - Form_, Report_.

[Ruegen]

You have PaymentDateBanked1st in the expression twice, should one be PaymentDateBanked2nd?

However, doubt that will resolve issue of 3rd term.

The expression with 3 terms is longer than 255 characters but I didn't think that was the limit on expressions.

The . provokes intellisense popup tips in VBA.

Forms!frmCompanyTaxInvoices. will not trigger intellisense but Form_frmCompanyTaxInvoices. does.

The first is used by Access in objects, the second is VBA exclusive. Look at the objects listed in VBA editor and note the prefix - Form_, Report_.

so renaming the fields to say p1, p2, p3 d1, d2, d3 could help solve the 255 character limitation? (I could make some fields in the record source of the report) i.e. p1: PaymentReceived1st

I will now be using form_ in vba from here on

[Ruegen]

It was character limitation!

I made each in the record source to a duplicate shorter version

Payment1st
tblTeacher


became
p1: Payment1st
tblTeacher

This solved the problem and I get no more #error!

[Ruegen]

Out of Curiosity - if you made it a memo field or longer character on the textbox would that also solve the problem?

[June7]

Length of expression in ControlSource property has nothing to do with field content.

[Ruegen]

Length of expression in ControlSource property has nothing to do with field content.

is there an option to increase it anywhere?

[June7]

There is not.

[Ruegen]

There is not.

Thank you very kindly for the help June7 - huge learning curve for me but you make it easier.

Cheers!