How do I make a Criteria that changes by row?

[cjthornton49]

Hello,

I have a table where the rows are line item entries for insurance claims. I want to populate a field that will look all the line items for a claim and return the maximum payment on each line of the claim. In the below table I'm trying to calculate "Max_Paymen t"


Claim_No Line_No Payment Max_Payment
T004 1 125 200
T004 2 50 200
T004 3 200 200
A012 1 45 45
B100 1 300 300
B100 2 250 300

I think I should be using DMax() but I'm unclear about how to write my criteria.

Thanks!

[Seth Schrock]

The criteria would be

"Claim_No = '" & Claim_No & "'"

[cjthornton49]

Access is changing this to

"CLM_NO_LTS T = '" & "CLM_NO_LTS T" & "'"

and the field is not populating. Any ideas?

[Seth Schrock]

Are you doing this in a query or as the Control Source of a textbox on a form?

[cjthornton49]

In a textbox on a form

[Seth Schrock]

Try building it in the Expression Builder and see if it stays that way. If not, please post your entire control source (in CODE tags).

[cjthornton49]

I got this to work on my tiny testing file, running it on the main table for awhile now, not sure if it's gonna get there or not.

Thanks for your help!