Help selecting Max of fields in a record

[fpperry]

I have a form that enters variable data to a table, 12 fields. I want to access that record and evaluate it for the maximum value in the record. I've tried several approaches but i can't seem to open the table from VB and read the fields of the 1st record. Can someone please assist?

 

[WayneRyan]

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=40209

Wayne

 

[Fornatian]

You coudl use a UNION query to stackyour values together in a column then run a max against it:

SELECT tblFindAcrossFields.N1 as FieldToMax
FROM tblFindAcrossFields; UNION
SELECT tblFindAcrossFields.N2
FROM tblFindAcrossFields;UNION
SELECT tblFindAcrossFields.N3
FROM tblFindAcrossFields;UNION
SELECT tblFindAcrossFields.N4
FROM tblFindAcrossFields;UNION 
SELECT tblFindAcrossFields.N5
FROM tblFindAcrossFields;

then run the max query against it:

SELECT Max(maxqrytest.FieldToMax) AS MaxOfFieldToMax
FROM maxqrytest;