How to use the last date from a table in a report

[lbcarvalho]

SYS_INVOICE_Copy.zip

Not sure if I did it right the first time so here it is again.

[June7]

Opened report and everything calculates properly.

[isladogs]

Opened report and everything calculates properly.

That surprises me! Texto15 & Texto17 are fine but Texto19 is blank.
That's partly due to the table name being mistyped in the expression as 'Limiteitau' instead of 'Limiteltau'
When corrected I get #Error

This is a simpler expression and should work but I also got #error for this

=DLookUp("ValueOfLimit","Limiteltau","LimitID = " & [Texto19])

Note that I renamed the Codrigo(?) field as LimitID to avoid issues with accented characters on my tablet.

[June7]

Okay, weird. Just opened report again to double check and all 3 textboxes show calculated value, no errors. I did nothing to the expressions.

[RagJose]

Lincoln,


first of all, if I understood what you're after, you'll still have to insert the fields you want to see listed into the Detail section of your report.
These fields must come from query "PmtsViaItau", which you declared as the RecordSource for the report.
And your report will only show content when you have Itaú payments more recent than the latest [DateOfLimit] in LimiteItau, which is not the case with the sample data you uploaded.


About the problem you are having:
The Header of your report shows as Texto15 the latest date appearing as [DateOfLimit] in table LimiteItau , and as Texto19 the last value for [Código] in the same table (please note that these two textboxes do not conceptually refer to the same record in LimiteItau). Both textboxes function as expected.

The other (big) textbox seems intended to show the value corresponding to the latest [DateOfLimit], but it showed up blank as I opened the DB after download (same as for you and for isladogs earlier today).
I can't explain that. The syntax june7 provided is correct (upper or lowercase don't matter for table or field names, at least in Windowsland), and I did succeed in having it show a value by just playing with the dates in LimiteItau. Weird -- it seems to function as expected only when I make the second LimiteItau record the one with the latest date. Another Access bug?!?

Now for a suggested solution, assuming the records in LimiteItau will always be in strictly ascending date order (if not, sorry, couldn't devise another workaround that doesn't use code):
Substitute
DLast("DateOfLimit","LimiteItau") and
DLast("ValueOfLimit", "LimiteItau")
as the ControlSource for the two main textboxes in your report header. I think you don't really need the [Código] textbox. Try it and let us know.

Additional advices:
[PmtReceived] seems to be expressed either in EUR or in USD. You'll want to use [GrossPmtReal] as a criterion instead, to avoid fake results in your report.
Also, you should normalize your DB, replacing the applicable text fields in "Pmt RECEIVED" and "TRABALHOS" by pointer fields to CLIENTES and tblPayBank. The Analyze Table tool (Database tools tab) can assist you in that.