Max of Query Record

[kagoodwin13]

I am trying to take the max across a query record. I've tried to use Max in the Expression Builder of my query, only to find out that Max isn't a recognized function in this section, even though it is in other parts of Access (rage!). I might have possibly set up my database wrong, but now I'm stuck with it, and trying to work around it.

My query results look like this:

PART_NUMBER	JAN14	FEB14	MAR14	APR14
1234567890	1000	2000	1000	2000
1234567891	2000	3500	4000	3000

I would like to get the max across each row:

PART_NUMBER	MAX
1234567890	2000
1234567891	4000

I tried Max([JAN14],[FEB14],[MAR14],[APR14]) but that didn't work.
Any ideas?

[June7]

Requires a custom function to calculate row-level statistics.

Here are examples

http://support.microsoft.com/kb/209839

http://allenbrowne.com/func-09.html

[kagoodwin13]

Great, this worked! However, the output of the MaxOfList function seems to be text.

Using the query above, I have one query that evaluates for the Max, MAX: MaxOfList([JAN14],[FEB14],[MAR14]), which gives the desired output below:
QUERY1

PART_NUMBER	MAX
1234567890	2000
1234567891	4000

However I would like to evaluate the [MAX] field and give a judgment: JUDGMENT: Switch([MAX]>3500,"RED",[MAX]<3500,"GREEN")
But I keep getting this as a result:
QUERY2

PART_NUMBER	MAX	JUDGMENT	Should be
1234567890	2000	#Error	GREEN
1234567891	4000	#Error	RED

Same result if I use an IIf statement like JUDGMENT: IIf([MAX]>3500,"RED","GREEN")

From my investigation, the output of QUERY1.[MAX] is indented to the left, like a text field, and I can't change the field properties of it to number or percentage.

EDIT: I made QUERY1 into a Make Table query, which saves into a table that re-defines [MAX] as a number. Thanks for your help June7, you've helped me a lot with numerous projects.

[June7]

Should avoid reserved words in naming convention. Max is a reserved word.

The value returned is text because the function is declared as variant.

What if the value is exactly 3500? It won't be captured by either condition.

Shouldn't have to resort to make table. Convert the value back to number with Val() or CInt() or CDbl() or CLng() function.

[kagoodwin13]

Val() worked. Thanks again!