I would like to find the largest value for each row in a query. I have a query with several fields, each field has an expression that produces an integer. Max and DMax seem to pertain to the values in a single field; I need to evaluate values from multiple fields in a single row. I keep seeing 'range' mentioned but I have yet to see any examples of evaluating a series of data like 'col1;col2;col3' or anything remotely similar. Does anyone know how to accomplish what I want in an Access query? Thanks.
Largest value in row
- Thread starter ThisGuy
- Start date
- Local time
- Today, 07:47
- Joined
- Aug 30, 2003
- Messages
- 36,270
YES! It works almost perfectly. Only problem is the Maximum function does not seem to like empty records. I think I can resolve it by wrapping each field in the series in an if statement, like IIf([Field1]="",0,[Field1])
I am dealing with 15 fields. Some records hold an integer, some are blank. It seems that the 2nd through the 15 field can be blank, BUT there has to be a number in the 1st field, otherwise the maximum function returns a blank. Also, Not able to get the dang iif statement to work for some reason.
EDIT:
EDIT:
I could not get iif(field = "","0",field) to work no matter what, nearly tore my hair out. It would seem that NULL != "", or I did not format it correctly (which I tried every permutation of with/without []/()/etc I could think of), cause using Nz() function is working perfectly.
Last edited:
If you values are only ever going to be from 0 and above (i.e. no negative numbers) then you can change this line:
Otherwise in place of -1 use -99999999999.
No need to use IIF() or Nz() to wrap your fields.
Code:
If Nz(FieldArray(I), -1) > Nz(currentVal, -1) ThenNo need to use IIF() or Nz() to wrap your fields.
Yes! The modified Maximum function is working now, without having to wrap the individual fields. Thank you pbaldy & vbaInet.
Throughout all your posts I hear faint whispers of 'improperly structured tables'. Maybe its just the wind or my hearing aids' batteries are going bad, but I have to ask: Can you post some sample data of these multiple fields, provide field names and give a brief explanation?
