MAX function different to Excel Max - why? (1 Viewer)

Hello everyone,

After trawling through data and queries for days I have finally stumbled upon the reason figures weren't matching which is because when the Max function is given the values ("3", "L") it returns "L" in MS Access and yet in MS Excel the same Max function returns "3". Firstly - does anyone know why this would be the case and the logic behind it?

Secondly, is there anyway to get MS Access to treat numbers as being higher in the ranking order than L. I could write a VBA function to do this but no time - is there any standard way of doing this?

Also this is mega frustrating - why oh why would this be the case Microsoft?!

Thanks for any help anyone can offer.

I doubt very much that the function works in the same manner, as your results show. The character "3" precedes the character "L" in a sort based on strings, which you presented here, so the value of character "L" is larger than the value of character "3".

AS far as I know, the Excel Max discards non-numeric values, and hence returns 3.

What are the possible ranges of your numbers and characters? It could be there is an issue here in storing data of different type in the same place, and yet trying to rely on the data type in the sort sequence.

It is a trivial task to write a function to order characters using their ASCII codes by any sensible pattern.

Use the ASC() function to get the code. UCase and LCase are also useful for moving the alpha characteers either above or below the numerals.

Cheers for your help guys yeh it is because Excel ignores non-numerics- can't believe I didn't think of that!

I'll write a function to sort it.

cheers

Where is this MAX funtion in Access? I didn't think there was one

There is a MAX funtion in SQL. But that takes a fieldname as an argument.

You can use excel funtions in Access if you like. Just add the Excel object library then use something like this:

excel.worksheetfunction.Max(1,5,20,4,18)

- returns 20 hopefully!

Ok might be slightly off topic but I asked this ages ago and got nowhere - how can you create a custom VBA AGGREGATE function i.e. that takes records as a parameter and can be used in a query in the same way aggregate functions can be.

I know I can create a function that uses a recordset object and puts the results into an array and manipulate from there etc. but is there anyway to create a custom function and then pass values into the array by using an SQL query e.g. instead of

I could use
Is there any way to do this?

PS. Stopher yes I was on about the SQL MAX function.

Many thanks for any input