Dmax in Query

[Brent]

I have a query that is pulling a lot of information from 2 tables for
accounts receivable and payments. I would like to include the date of the
last payment in my output while summarizing all AR and all payments. Can
this be accomplished with the dmax statement? Also I am a novice so how
would the filter portion of that statement look.

ex. current query is called qryar
dmax("date",tblpayments,account# = ??????)

basically how do I tell it to look at the current record in the query.

Thanks
Brent

 

[Ken Snell [MVP]]

DMax("[date]","tblpayments","[account#] = " & [NameOfAccount#FieldInQuery])

This may make the query run a bit slowly.

Note that it's not a good idea to use Date as the name of a field. See these
Knowledge Base articles for more information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

 

[George Nicholson]

Any chance that tblPayments is one of the 2 tables in your query? If so, you
may be able to simplify and avoid Dmax:
LastPayment: Max([tblPayments.Date])
This assumes you are already grouping on Account#

otherwise you would need to use Dmax, per Ken's response.

 

[Brent]

Thanks for the responses. I did use George's response and it is working
well.

You guys are great!
Brent

DMax("[date]","tblpayments","[account#] = " &
[NameOfAccount#FieldInQuery])

This may make the query run a bit slowly.

Note that it's not a good idea to use Date as the name of a field. See
these
Knowledge Base articles for more information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

--

Ken Snell
<MS ACCESS MVP>

I have a query that is pulling a lot of information from 2 tables for
accounts receivable and payments. I would like to include the date of
the last payment in my output while summarizing all AR and all payments.
Can this be accomplished with the dmax statement? Also I am a novice so
how would the filter portion of that statement look.

ex. current query is called qryar
dmax("date",tblpayments,account# = ??????)

basically how do I tell it to look at the current record in the query.

Thanks
Brent