We already confirmed that CanShrink is not the issue. Doesn't matter the size of textbox, records are still there.
We already confirmed that CanShrink is not the issue. Doesn't matter the size of textbox, records are still there.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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).
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.
Updated to working
https://www.dropbox.com/s/pim0hq2vxw...atabase3.1.zip
put 5/1/2013 in for the date field
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.
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)))
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
edit - will check this oneThe 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)))
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...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 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...
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.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
ok I will give that a goI 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 sumI 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.
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 did what you suggested however I am still not getting a sum
https://www.dropbox.com/s/zras2ztmvb...atabase3.5.zip
I will see if I can replicate this on the company database - should work the same
fingers crossed*
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?