Dmax in a calculated field

[CanWest]

I have a query with a field called TotalToDate

I have placed the following DMax function in that field

DMax("nz([TotalToDate])","tbl_ProgramInvoiceLineItem","[Service]=????")+[ServiceCost])

The question Marks represent the criteria part I can not figure out

Basically i want the function to return the max value of the TotalToDate field where the Service field in the query is the same value as the service field in the table.

I hope I am making sense. I have searched and can not find anything here like this though I am certain that it is here. I just can't find it. Any help would be gratly appreciated

 

[plog]

Could you give the structure of all the tables that comprise your query, the structure of tbl_ProgramInvoiceLineItem and the SQL of the query.

Also, how come tbl_ProgramInvoiceLineItem isn't part of this query?

 

[CanWest]

The sql is as follows

INSERT INTO tbl_ProgramInvoiceLineItemStep4 ( ProgramID, ServiceType, Service, ServiceCost, ServiceMaxCost, InvoiceNumber, Quantity, Total, TotalToDate )
SELECT tbl_ProgramInvoiceLineItemStep3.ProgramID, tbl_ProgramInvoiceLineItemStep3.ServiceType, tbl_ProgramInvoiceLineItemStep3.Service, tbl_ProgramInvoiceLineItemStep3.ServiceCost, tbl_ProgramInvoiceLineItemStep3.ServiceMaxCost, tbl_ProgramInvoiceLineItemStep3.InvoiceNumber, tbl_ProgramInvoiceLineItemStep3.Quantity, [Quantity]*[ServiceCost] AS Total, DMax("nz([TotalToDate])","tbl_ProgramInvoiceLineItem","[Service]=[tbl_ProgramInvoiceLineItem].[Service]")+[ServiceCost] AS TotalToDate
FROM tbl_ProgramInvoiceLineItemStep3;

The only part that is not working correctly is the DMax function which seems to be ignoring the criteria