Display highest number from 3 access fields

[oggstar1]

I have 3 feilds in a access databse with numbers in all fields

[score1]4
[score2]6
[score3]8

I would like a formula to determine the highest number in the three feilds and display in a seperate feild

Max number;[score1][score2][score3]

Answer = 8

Any ideas on the code or function.

 

[Bastien]

I would suck each element into an array, then sort the array and show the highest value that way...

Bastien

Any one have a techie job in Toronto, I need to work...being laid off sucks!

 

[oggstar1]

Thanks for the advice Bastien

Any help with the code or function would be appreciated. Sorry should have mentioned that i am just starting to learn VB.

 

[Bastien]

read here...gives code and example

Bastien

Any one have a techie job in Toronto, I need to work...being laid off sucks!

 

[TonyJollans]

Hi oggstar1,

Are you wanting to do this in code or a query or somewhere else? If there are only three fields, I would check them explicitly ..

Iif([Score1]>[Score2],Iif([Score1]>[Score3],[Score1],[Score3]),Iif([Score2]>[Score3],[Score2],[Score3]))

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[oggstar1]

Thanks Tony Jollons

Works well, although I do have over six fields and the above function would start to get a little complicated with additional fields.

Is there something similar to the excell function
MAX(A1:A5)in access

Oggstar1

 

[TonyJollans]

Hi oggstar1,

Sorry, afraid there isn't. You have to do it the hard way.

I know it's easy for me to say and might not be easy for you to do anything about, but if you have a lot of requirement for this it suggests your data are not normalised.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.