Max Value Problem

[m17347047711116]

O.K i know this may be a normalization problem but i need to find a way to work with what i have.

Situation:
Each time my AGV's move they send a data transaction to a data base that logs the Date, Time, AGV #1 Sec, AGV#2 Sec, AGV#3 Sec.
Problem:
I would like to know the AGV that had the maximum value for that record.
I think I will need to manipulate this data some how but i don't know how.
I want to try this expression in a query:
Max(AGV#1,AGV#2,AGV#3)
But it won't work
I can't change the way i get the data can someone give some help on how to manipulate this data so i can find the max value of several values in a record.

 

[raskew]

Not sure if this is what you're after. Here's query results for a simple table:

tdate	ttime	AGV1	AGV2	AGV3	MaxValue
3/5/03	10:00	5	8	3	8
3/5/03	11:00	4	2	6	6

You could use this function:

Public Function zMax(ParamArray values() As Variant) As Variant
  Const loend As Double = -1000
  Dim val As Variant
  zMax = loend
  For Each val In values
    If val > zMax Then
      zMax = val
    End If
  Next val
End Function

-in a query, which would be similar to this:

SELECT Table12.tdate, Table12.ttime, Table12.AGV1, Table12.AGV2, Table12.AGV3, zmax([AGV1],[AGV2],[AGV3]) AS MaxValue
FROM Table12;

 

[m17347047711116]

Public Function zmax

O.k This looks like what i need
zmax code

but how do i put this code into a querie ???

 

[raskew]

Refer to the original response. The last example is query-SQL--

SELECT Table12.tdate, Table12.ttime,
Table12.AGV1, Table12.AGV2, Table12.AGV3, zmax([AGV1],[AGV2],[AGV3]) AS MaxValue
FROM Table12;

-with the call to zMax() in bold

 

[m17347047711116]

Got It

Sorry, had the query expression correct, had to put the code into VB editor for the page.

thanx

Works Great