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

[lbcarvalho]

Hi, every1,



I have a table1 with DATE and AMOUNT. I need to build a form based in another table (table2) that shows only the records with dates AFTER the last date from table1. Is it possible to do this without coding or using macros?

Thanks!

Lincoln

[June7]

Date is a reserved word. Should not use reserved words as names for anything.

SELECT * FROM table2 WHERE [Date]>DMax("[Date]","table1");

or

SELECT * FROM table2 WHERE [Date] > (SELECT Max([Date]) FROM table1);

[lbcarvalho]

Can I use these in a query too?

[June7]

Those are queries. Switch query designer to SQLView and you can see the SQL statement that Access builds for you.

[lbcarvalho]

Thank you! Thanks to your help I managed to place one of the fields I wanted in my report, but now I would like to place a second field that matches the first one. That is, I have a table called LimiteItau and it has two fields for each record: ValueOfLimit and DateOfLimit. I have used this to get the latest date into my report: =DMáx("[DateOfLimit]";"LimiteItau"). Now, how do I place the field ValueOfLimit next to the field DateOfLimit?

[June7]

=DLookup("ValueOfLimit","Limiteitau","DateOfLimit= #" & DMax("[DateOfLimit]","LimiteItau") & "#")

or try

=DLookup("ValueOfLimit","Limiteitau","DateOfLimit= #" & [textbox name that has the Max date] & "#")

[lbcarvalho]

Thanks again, June7, but the first option shows a blank field and the second shows #ERR...

[June7]

The technique is correct and works for me.

I don't know your db.

However, you show semi-colon where I have comma. Presume you are not in U.S.

[lbcarvalho]

You are right, I'm in Brazil and my MS Access is set to this country. However I have changed the colons to semi-colons, so this isn't an issue. The table from where I want to get the pieces of info is quite simple :
It has two fields per record. All I want to do is get the latest value and latest date (this I could do) and put them into a Report. Your first suggestion worked like a charm, but the second one is not working for me and I still don't get to figure why.
I hope I have made it all clearer now and would appreciate if you could try to help a little further.
Thanks again!

[June7]

DateOfLimit field is not date. A date has only 3 parts (not counting time) - day, month, year. You show 4 parts. This is text.

I doubt the latest date query could be valid. Are you sure it returns the latest? I don't know month names in Portuguese. Alphabetically, the query will return "seg/06/mai/19" as the max value.

Text data uses apostrophe delimiter instead of # character. However, the results will still not be valid because alpha sort rules apply.

[lbcarvalho]

Please have a look at the way I set up my date. ddd/dd/mmm/yy will show the day of the week / the day / month / year.
As far as I know (ok, I don't know much ) Access will recognize this field as date, since it allowed me to choose a date...

sáb = sat(urday) / seg = mon(day) / qui = thu(rsday) and set = sep(tember) / mai = may

[June7]

Oops, I missed the Totals line in the first image. That would have clarified for me if I had read it.

This is why I do not set formatting or lookups in table. I prefer to see the real data when I view tables.

And I am now very confused. Since format property does not actually change the data, the syntax I provided should work. It does for me.

I cannot replicate issue so I cannot advise how to fix.

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

[RagJose]

Hi Lincoln, I'm Brazilian like you. If you're still stuck, I'm willing to try helping. Just let me know.

[lbcarvalho]

Hi! I have uploaded a leaner and less cluttered DB to AccessForum. Please have a look. I can also record a video and upload it to YouTube if you guys find necessary. Thanks again!

[lbcarvalho]

Hi, RagJose,

I have just uploaded my DB. Please have a look and let me know your thoughts. I'm not an Access expert, so it may seem a little bit messed up.