Max date in report criteria

[vandaliarental]

I'm using vb to control several reports based on last month & previous dates. Most work fine except for the one below:

Public Function PrintReports()
Dim criteria As String
Dim NinetyDaysAgo As Date

NinetyDaysAgo = DateSerial(Year(DateAdd("m", -3, Date)), Month(DateAdd("m", -3, Date)), 1)

criteria = &quot;#&quot; & DMax([CallDate], [cust_id], [Calls]) & &quot;#<#&quot; & NinetyDaysAgo & &quot;#&quot;

DoCmd.OpenReport &quot;CustomersNotCalledOn&quot;, acViewNormal, , criteria

The CustomersNotCalledOn report is based on a query that retrieves the last time a customer was called on from a table called Calls which stores the customerID and CallDate.

The sql for the query is:

SELECT [Customers].cust_id, Max(Calls.CallDate) AS MaxOfDate
FROM [Customers] INNER JOIN Calls ON [Customers].cust_id = Calls.CID
GROUP BY [Customers].cust_id;

This worked when I had a prompt in the query for the date, but I took it out so the user wouldn't have to enter it. I know I have something wrong with the DMax statement, but I just can't figure out what. I can't get it to group the call date by customer.

Any ideas???

 

[gol4]

Hope this don't double post errored out on me

if this is a cut and paste then the problem is a parenthesis
in the middle of you dmax statement. move to the end

criteria = &quot;#&quot; & DMax([CallDate], [cust_id], [Calls]) & &quot;#<#&quot; & NinetyDaysAgo & &quot;#&quot;
needs to be
criteria = &quot;#&quot; & DMax([CallDate], [cust_id], [Calls]& &quot;#<#&quot; & NinetyDaysAgo & &quot;#&quot