Query Function? Max of options

[dantebothermy]

I'm looking for a function that is similar to one in excel:

=max([field1],[field2])

I know I can write it using an iif statement, but I'm looking for an easier approach. Any ideas?

Thanks,


Dante

[ChipR]

Unless you prefer to write your own comparison function, it doesn't get much easier than iif.

[dantebothermy]

I'm sorry to hear that. But thanks

[Whizzo]

What a coincidence, I've just hopped on here to find an answer for the exact same question! How is this done with IIF()? All I want is for the query to look at column1 and column2 and place the highest figure it finds in column3. As far as I can see IIF() can only compare A to B or B to A, not both. Any ideas?

Thanks!

[Whizzo]

Scratch that,

IIf([PolicySumAssuredLife1]>[PolicySumAssuredLife2],[PolicySumAssuredLife1],[PolicySumAssuredLife2])

seems to hold water. All solved!

[NeoPa]

There's no function native to Access that provides this functionality.

You can write your own Public function if you like, otherwise make do with IIf().