Sorting Problem

[sonnierock]

I got this problem

Name | Time1 | Time2 | Time3 | Time4 | Min Time|
==========================================
Tom | 12:13 | 07:00 | 17:00 | | 07:00 |

==========================================

Min Time is the Query result I need to get.


I know to Group by and sort them by Min if the time is in
the same column but when they are in diffrent column and
one of them is a null...........crazy

Then I wrote iif([time4] is null,.....,.....) and after
having program everthing Access say "The string return by
builder was too long. Message is truncated.

Thanks for your help,

 

[The_Doc_Man]

Your initial problem is table design. You should have three columns in the table:

Name, time, and time-code (that tells you whether it is time1, time2, etc.)

If you can't rebuild your data table to get there from here, the other alternative is to write a UNION query that selects the name and one of the time columns (plus the constant "Time1" or "Time2" etc.) Look up UNION queries in the Help Files. Then build a UNION query with four branches, one for each column of times. After that, the Minimum time can be found for each named person with a Totals query that does a Group-by on the name and a Min on the times.

A further note: If those time fields are TEXT, you will need to convert them to dates using CDat(time-string). Otherwise, you will run into ordering problems with variable-length strings.

 

[Jon K]

Or you can create a public function MinTime() in a Module:-

----------------------------
Public Function MinTime(Time1, Time2, Time3, Time4)
Dim arrMin
Dim MinValue
Dim i As Integer

' put values in an array
arrMin = Array(Time1, Time2, Time3, Time4)

' initialise MinValue, ignoring Nulls
MinValue = IIf(Not IsNull(Time1), Time1, IIf(Not IsNull(Time2), Time2, IIf(Not IsNull(Time3), Time3, Time4)))

' loop through array to compare values
For i = 1 To 3
MinValue = IIf(Not IsNull(arrMin(i)) And arrMin(i) < MinValue, arrMin(i), MinValue)
Next

' return the minimum value
MinTime = MinValue

End Function
____________________________

and use the MinTime() function in your Select query:-

SELECT Name, Time1, Time2, Time3, Time4,
MinTime(Time1,Time2,Time3,Time4) AS [Min Time]
FROM yourTable;


A function has no length limit and is much easier to read and debug than nested iif() functions in a query.

 

[sonnierock]

thanks Guys

Thanks for your Advise.

The_Doc_Man and Jon K. U gus are super...


Sonnie