Return a date in a Query

[Smack]

Hello. I was hoping that you may be able to help me with a query that a report is built from. In the mdb I created, I have a form with a subform (Table - Subtable) What I need from the query is to return only the most recent date entered into the subform date field. This would seem like an easy thing to do, but I just cannot noodle it out! I do not know how to write code or SQL, so I typically end up doing things the hard way.
Best regards,
Smack

 

[RickSpr]

I hate to be picky, but because of the way you've phrased this, I'm not sure what you want. You said you needed a query to return the most recent date entered into the subform date field. But a query retrieves data from a table, not a form. So I'm trying to figure out how the form comes into it.

Do you want the latest date in the subtable, period? That's easy: Create a query on that table, drop the date field into the grid, make it a Totals query (choose View>Totals), and set the Totals line for the date to Max.

Or do you want the latest date entered via that form, as opposed to those entered via another form, or via a table or query datasheet? To do that, you'll have to store another column in the table, to indicate where the row data comes from, then use that column in your query criteria. (Other than that, use Max in a totals query as above.)

Or do you want the latest date entered into that form in the current session, that is, since the form was last opened. Or something else?

If I haven't yet guessed how the form fits in with your question, please provide additional info. Rick Sprague

 

[Smack]

Rick, I had not been getting a response in this forum so I placed it in the general discussion forum also - there is much more detail in there. The form/subform are based on a Table/subtable. Itried using max on the date field in the query containing both tables, and also with just the subtable alone. I still get multiple dates returned for the same recordset, not just the last date entered as I need it to be. Apologies for the duplication of the question.
-Smack

 

[TheCleaner]

I used the "Top Values" button in a query that sorted the records with some other fields that I needed. However, I used this with a Record number not a date, don't know if that will work. Hoped that helped.