Dmax in Query

B

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
 
K

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
 
G

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.
 
B

Brent

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

You guys are great!
Brent
Ken Snell said:
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>


Brent said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top