Query based on Max Value - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Query based on Max Value

Helen Renaud

3 years ago

Hello: I am using the instructions in Max Value from Multiple Fields in Microsoft Access video and it works perfectly, except in one record. It defies all logic that only one Max Value returned in the Query is wrong, but it is. I found this when I matched the values to an Excel file with the same data. I do not understand why only one is not working. I am trying to take the maximum from 4 fields, within another query. I think this question may be vague, but I don't want to show what was in the Extended cut of the instruction video.

	Helen Renaud OP @Reply 3 years ago

	Helen Renaud OP @Reply 3 years ago
If you look at the first two rows, it works. X is the max of the last 5 columns. So, 39.74 is the maximum. Then on row 3, 15.30 should be the maximum, but x column shows that 9.43 is the maximum, which is wrong.

	Kevin Robertson @Reply 3 years ago
It looks like they are text fields (aligned left). If you change the data type to number you should get the correct values.

	Kevin Robertson @Reply 3 years ago
Remember to change the Field size to Double to maintain your decimal values.

	Helen Renaud OP @Reply 3 years ago
Thank you for your response. The last 5 columns are formatted as "standard". I just changed the "x" column to standard too. But then again, why are the first two rows correct? Could it have something to do with the VBA code that was in the Extended video cut? It states.....Dim X As Long

	Helen Renaud OP @Reply 3 years ago
I figured this out. You led me in the right direction, Kevin. Even though I had formatted the numbers to standard, in the query, I had to also add CDbl to force it to change it to a number. Eg. RT_Calc_Inch: Format(CDbl([M3_Calc_Inch]/2.5),"Standard")

	Helen Renaud OP @Reply 3 years ago
Ugh...No that did not work. I had to remove the "standard" completely. Now my numbers have 10 decimal places in the query and report. Is there a way to fix that? Even thought I have the field as "standard" in the report, it still shows too many decimals.

	Kevin Yip @Reply 3 years ago
Hi Helen, "9.43" is greater than "15.30" if they are treated as text. Text comparison is done from left to right. If the first character is "9", the string will be greater than any string whose first character starts with "8", "7", etc. So if you want to compare them as numerals, they need to be converted to numerals. Your query needs to express the fields as numerals.

Helen Renaud OP @Reply

3 years ago

Hello Kevin Yip. Thanks for the response. I see how that was calculated now. But, after my initial post, the query has been changed to have all fields with CDbl. So, they are all text now and the formula showing the maximum value is correct. The issue now is that it has too many decimal places and I would only like two decimal places. It sounds easy, but nothing works to convert them to two decimal places. Any suggestions would be welcome.

	Kevin Yip @Reply 3 years ago
You can use the Round(M, N) function to round a value M to N decimal places. It works with both numerals and strings that look like numerals. Both of the following: Round("1234.56789", 2) Round(1234.56789, 2) will return 1234.57

	Scott Axton @Reply 3 years ago
Helen for future reference: There is a special forum where you CAN post regarding material concerning extended cut topics. At the top menu Forums > scroll down to Other forums. There is a TechHelp Members Forum. Here is a direct link: TechHelp Members Only

	Helen Renaud OP @Reply 3 years ago
Thank you Scott and Kevin Yip. Kevin, I don't know why I didn't try "round". Thanks for the reminder. Also, I see an error in what I wrote, I said that they fields are now all text, but I meant numeric! Thank you all very, very much!

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View