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

[June7]

We already confirmed that CanShrink is not the issue. Doesn't matter the size of textbox, records are still there.

[Ruegen]

We already confirmed that CanShrink is not the issue. Doesn't matter the size of textbox, records are still there.

yes but I want to make sure those fields that do not match the date are not summed with the fields that do...

[June7]

And that's where you lose me because I don't understand your data. Sounds like a condition dependent on data in same field of another record and maybe you need another group in the report. The date changes, that is a new group.

[Ruegen]

Here is a basic idea of what I am attempting to acheive - however for some reason the combo box isn't working correctly as it should show results in on the record that match the dates - I figure you will be able to see which anyway

https://www.dropbox.com/s/5ecncgot1pddi6t/Database3.zip

now if you made any textboxes null if the corresponding date wasn't the same as txtDate then you would shrink it and not sum it with the rest

money1 and money1date are together even though they are on the same record (we just have to understand they are even though this is not the correct way of doing it).

now if money1date does not equal txtDate then
null
money 1 and money2

and then

if null, report will collapse those fields with canshrink (just to avoid showing the fields with the dates that do not match).

[Ruegen]

For example this record shows the two different payment fields however the dates are different (even though the booking date is the same) since the payment date is different - I have that become null and hide using canshrink - then I want to sum all the payments I see even though they are different fields.

field1 (if not made null) + field2 (if not made null)

now sum any fields either field1 and field 2 across all records that are still showing...

the 18/11/2013

and 25/11/2013

are the same record - just different fields.

[Ruegen]

Updated to working

https://www.dropbox.com/s/pim0hq2vxw...atabase3.1.zip

put 5/1/2013 in for the date field

[Ruegen]

And an example of the error I get

https://www.dropbox.com/s/j0ptfnsndg...atabase3.2.zip

(if summing text fields doesn;t work this will explain the error I get)

I had to rearrange the junction to get proper results so I will update this later to give a better example.

[June7]

The last sample db provided is not set up as described in your posts. There are no textboxes calculated to null and none are set to shrink. However, I might have some understanding now.

If any of the pay dates does not equal the criteria date, don't include that payment in the sum? Maybe:

=Sum(IIf([DatePaid1]<>[txtDate],0,Nz([Money1],0))+IIf([DatePaid2]<>[txtDate],0,Nz([Money2],0))+IIf([DatePaid3]<>[txtDate],0,Nz([Money3],0)))

[Ruegen]

The last sample db provided is not set up as described in your posts. There are no textboxes calculated to null and none are set to shrink. However, I might have some understanding now.

If any of the pay dates does not equal the criteria date, don't include that payment in the sum? Maybe:

=Sum(IIf([DatePaid1]<>[txtDate],0,Nz([Money1],0))+IIf([DatePaid2]<>[txtDate],0,Nz([Money2],0))+IIf([DatePaid3]<>[txtDate],0,Nz([Money3],0)))

edit - will check this one

I get an error if I put it both in the page and report footer

I don't get it calculating sometimes (0) and other times it calculates both...

https://www.dropbox.com/s/3z6mhlde5s...atabase3.4.zip

[Ruegen]

edit - will check this one

I get an error if I put it both in the page and report footer

I don't get it calculating sometimes (0) and other times it calculates both...

https://www.dropbox.com/s/3z6mhlde5s...atabase3.4.zip

I don't think it will work because when it gets 1 record and it is blank then it doesn't sum all the records for that one field...

I had discussions with work and I might have to just make a button that duplicates the record but hides it from common view to make extra payments on the same booking. - same date and copy over the teacher id into a new field just as a reference to relate it.

I'd have to sum each record first , then sum the summed records for this to work...

[June7]

I removed the dd/mm/yyyy formatting from txtDate textbox.

I fixed the Sum expression (I forgot to include form qualifier):

=Sum(IIf([DatePaid1]<>[Forms].[Form1]![txtDate],0,Nz([Money1]))+IIf([DatePaid2]<>[Forms].[Form1].[txtDate],0,Nz([Money2]))+IIf([DatePaid3]<>[Forms].[Form1].[txtDate],0,Nz([Money3])))

I removed the Requery line from the procedure because it's just unnecessary.

Now the form/subreport definitely work.

[Ruegen]

I removed the dd/mm/yyyy formatting from txtDate textbox.

I fixed the Sum expression (I forgot to include form qualifier):

=Sum(IIf([DatePaid1]<>[Forms].[Form1]![txtDate],0,Nz([Money1]))+IIf([DatePaid2]<>[Forms].[Form1].[txtDate],0,Nz([Money2]))+IIf([DatePaid3]<>[Forms].[Form1].[txtDate],0,Nz([Money3])))

I removed the Requery line from the procedure because it's just unnecessary.

Now the form/subreport definitely work.

ok I will give that a go

[Ruegen]

I removed the dd/mm/yyyy formatting from txtDate textbox.

I fixed the Sum expression (I forgot to include form qualifier):

=Sum(IIf([DatePaid1]<>[Forms].[Form1]![txtDate],0,Nz([Money1]))+IIf([DatePaid2]<>[Forms].[Form1].[txtDate],0,Nz([Money2]))+IIf([DatePaid3]<>[Forms].[Form1].[txtDate],0,Nz([Money3])))

I removed the Requery line from the procedure because it's just unnecessary.

Now the form/subreport definitely work.

I did what you suggested however I am still not getting a sum

https://www.dropbox.com/s/zras2ztmvb...atabase3.5.zip

[Ruegen]

I did what you suggested however I am still not getting a sum

https://www.dropbox.com/s/zras2ztmvb...atabase3.5.zip

wait, I put format(txt.datefield,"mm/dd/yyyy") on the after update vba and it works!

I will see if I can replicate this on the company database - should work the same

fingers crossed*

[Ruegen]

I'm getting an #error still on the company one

I am using

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


to hide the fields I am using (each is different but they follow the same practice)

=IIf([PaymentDepositDateBanked]<>[Forms]![frmCompanyTaxInvoices]![txtDate],Null,IIf(IsNull([PaymentDepositReceived]),Null,[PaymentDepositDateBanked]))


and my afterupdate is

Me.rptCompanyTaxInvoices.Report.Filter = "(PaymentDateBanked1st=#" & Format(Me.txtDate, "mm/dd/yyyy") & "# Or PaymentDateBanked2nd=#" & Format(Me.txtDate, "mm/dd/yyyy") & "# Or PaymentDepositDateBanked=#" & Format(Me.txtDate, "mm/dd/yyyy") & "#) And TradingAsID=" & Me.CmbCompany
Me.rptCompanyTaxInvoices.Report.FilterOn = True

could there be something I should be looking out for in theory that could cause the error?