473,322 Members | 1,690 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

How do I make a Criteria that changes by row?

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_Payment"


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!
Aug 6 '15 #1
6 1525
Seth Schrock
2,965 Expert 2GB
The criteria would be
Expand|Select|Wrap|Line Numbers
  1. "Claim_No = '" & Claim_No & "'"
Aug 6 '15 #2
Access is changing this to

"CLM_NO_LTST = '" & "CLM_NO_LTST" & "'"

and the field is not populating. Any ideas?
Aug 6 '15 #3
Seth Schrock
2,965 Expert 2GB
Are you doing this in a query or as the Control Source of a textbox on a form?
Aug 6 '15 #4
In a textbox on a form
Aug 6 '15 #5
Seth Schrock
2,965 Expert 2GB
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).
Aug 6 '15 #6
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!
Aug 6 '15 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

87
by: expertware | last post by:
Dear friends, My name is Pamela, I know little about CSS, but I would like to ask a question I have an image on a web page within a css layer: <DIV ID=MyLayer STYLE = "position:...
6
by: AAVF | last post by:
Hi We have a problem with a query. An Access database links via ODBC to a UNIX server. To speed things, we use the ODBC to load the relevant tables to the local PC that runs Access so that...
4
by: John | last post by:
Hi All, I have a problem making changes to a query. The query is extracting data from three different tables, but when I run the query in a form or datasheet view I can't make any changes to the...
1
by: max | last post by:
I have about 20 different querys but they all have some fields that use the same expression. I would like to add criteria to those fields without opening every query individually. Plus this...
5
by: ChadDiesel | last post by:
Hello Again, I want to assign a number to each record that will be part of a shipping number. I want the number value to count up until the contract number changes. Then, I want the number to...
3
by: google | last post by:
I'm developing an application for use within my company in Access 2003. I'm new to '03, the application I did for my former employer was in '97. The two applications have similar functionality...
5
by: DW | last post by:
I have a query in Access 2003 that has the following criteria SELECT tblOrder.SessionDate, tblMenus.Item_Name, tblOrder.Type, Format$(tblorder!SessionDate,"Short Time") AS SessionTime,...
10
by: aaronrm | last post by:
I have a real simple cross-tab query that I am trying to sum on as the action but I am getting the "data type mismatch criteria expression" error. About three queries up the food chain from this...
8
by: Dr Al | last post by:
I have a table with four date fields, some of which may not be filled in based on our data entry needs. I have a criteria set as <date()-180 which is supposed to pull dates older than 180 days ago....
0
by: sebtus | last post by:
I need help with this task... I have a form "myForm" which has multiple buttons on it, such as “John”, “Tom”.. etc, I have one Report "Parts Report" and Query "Parts Query" for that report, Query...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.