Finding the next value after the minimum

[tonyblack]

From the query result below i need to be able to state the earliest scan time from the 'MinTime' list and the latest scan time from the 'MaxTime' list. I have achieved this using Min & Max in my report but i have a problem in that i need to ignore any MinTime that is entered as 01:00:00 and find the next earliest time (i.e. 08:49:00)and present it in a report textbox.

ID KG Picked MinTime MaxTime
151319 109.55 09:41:00 14:27:00
150743 107.38 08:49:00 15:21:00
150859 103.55 09:44:00 15:17:00
150841 94.29 09:43:00 15:12:00
150878 90.46 09:34:00 14:58:00
150929 86.63 09:22:00 15:16:00
150839 82.29 10:21:00 15:09:00
151030 79.55 01:00:00 14:40:00
150651 76.29 10:17:00 15:16:00
150393 76.29 10:12:00 15:15:00
150373 70.29 10:38:00 15:18:00
150857 70.29 09:23:00 15:06:00
100100 70.03 09:10:00 15:42:00
100726 69.20 09:30:00 15:15:00

I hope i have explained my dilemma adequately. Many thanks in advance for any help.
Tony Black

 

[nagornyi]

Not sure where your times comes from, but in your select statement you may indicate something like
time_field not in (' 01:00:00 ')
or whatever depending on the field.

 

[Crowley16]

in the minTime box, put

iif(minTime = #01:00:00#, dlookup(minTime, tblName, minTime > #01:00:00#), minTime)

or something to that effect...

 

[tonyblack]

I hope this clarifies things. I need my report to look like this:

ID KG Picked MinTime MaxTime
151319 109.55 09:41:00 14:27:00
150743 107.38 08:49:00 15:21:00
150859 103.55 09:44:00 15:17:00
150841 94.29 09:43:00 15:12:00
150878 90.46 09:34:00 14:58:00
150929 86.63 09:22:00 15:16:00
150839 82.29 10:21:00 15:09:00
151030 79.55 01:00:00 14:40:00
150651 76.29 10:17:00 15:16:00
150393 76.29 10:12:00 15:15:00
150373 70.29 10:38:00 15:18:00
150857 70.29 09:23:00 15:06:00
100100 70.03 09:10:00 15:42:00
100726 69.20 09:30:00 15:15:00

Earliest scan time= 08:49:00
Last scan time= 15:42:00

At the moment the earliest scan time is showing as 01:00:00. I need the report to ignore scan times of 01:00:00 as they have been entered manually and are not accurate.

Tony Black

 

[Crowley16]

eh, did you actually do the things that we posted?

 

[tonyblack]

Could you clarify:

iif(minTime = #01:00:00#, dlookup(minTime, tblName, minTime > #01:00:00#), minTime)

Should i swap 'tblName' for the name of the query that this report is based on?

 

[Crowley16]

yes...

use F1 on the dlookup function

or you can use an sql statement...
SELECT TOP 1 minTime FROM qryName WHERE minTime > #01:00:00#;