How to get the previous, current and next values for a field

[DawnTreader]

Hello All

I have need to know in one "line" the previous value, current value and the
"future" value.

here is my scenario. i have a "service report", these can be entered at any
time and so sometimes people can be entering a service report for a past date
that "fits" between 2 other reports. the problem is that each time the same
product is serviced and a report is entered there are running hours.

this running hour value can only increase but if someone is putting in a
report that falls between 2 that are already entered then the running hours
must not be less than the previous report or greater than the "future" report
than the entered report is trying to fit into. this makes validating entries
something of a pain but it needs to be done.

i have a query that gives me the previous hours. is there a way to modify
this to also give me the next hours on the same line? if so i can get the
code to look up the 3 values in this query. here is the sql:

SELECT DISTINCTROW subtblRunningHours.RunningHoursID,
subtblRunningHours.RHDate, subtblRunningHours.HoursAtDate,
subtblRunningHours.ServiceReportID, subtblRunningHours.BlockID,
subtblRunningHours.IssueID, (SELECT TOP 1 DsubtblRunningHours.HoursAtDate
FROM subtblRunningHours AS DsubtblRunningHours WHERE
DsubtblRunningHours.BlockID = subtblRunningHours.BlockID AND
DsubtblRunningHours.RHDate < subtblRunningHours.RHDate ORDER BY
DsubtblRunningHours.RHDate DESC, DsubtblRunningHours.RunningHoursID) AS
PriorRH, (SELECT TOP 1 DsubtblRunningHours.RHDate FROM subtblRunningHours AS
DsubtblRunningHours WHERE DsubtblRunningHours.BlockID =
subtblRunningHours.BlockID AND DsubtblRunningHours.RHDate <
subtblRunningHours.RHDate ORDER BY DsubtblRunningHours.RHDate DESC,
DsubtblRunningHours.RunningHoursID) AS PriorRHDate
FROM subtblRunningHours
GROUP BY subtblRunningHours.RunningHoursID, subtblRunningHours.RHDate,
subtblRunningHours.HoursAtDate, subtblRunningHours.ServiceReportID,
subtblRunningHours.BlockID, subtblRunningHours.IssueID,
subtblRunningHours.HoursAtDate
ORDER BY subtblRunningHours.BlockID DESC , subtblRunningHours.HoursAtDate
DESC;

any and all help is appreciated.

 

[Dale Fye]

Why do the results all have to be "on the same row" in the query results?

Assuming you actually have a hard copy of the service report, and that you
are not creating one where it didn't really exist (forgive my cynicism), then
the report should already have a date and a HoursAtDate on the report, and
all you should have to do is enter that data.

If what you need is a way to confirm that the hours you have entered are
between the running hours of the previous and next records, then create a
query that will return those two values in two separate records:

SELECT RunningHoursID, RHDate, HoursAtDate
FROM subtblRunningHours
WHERE BlockID = Form!yourFormName.txtBlockID
AND RHDate >= (SELECT Max(RHDate)
FROM yourTable
WHERE BlockID = Form!yourFormName.txtBlockID
AND RHDate < Form!yourFormName.txtRHDate)
AND RHDate <= (SELECT Min(RHDate)
FROM yourTable
WHERE BlockID = Form!yourFormName.txtBlockID
AND RHDate > Form!yourFormName.txtRHDate)

Then, you could use the DMin("HoursAtDate", "query") and
DMAX("HoursAtDate", "query") to determine whether the value in your forms
HoursAtDate field is between these values.

 

[DawnTreader]

Hello Dale

the reason for one row is so that i dont have to dlookup multiple rows. that
would require 2 different sets of criteria and if they are on one row my code
is more efficient.

at least i think that was my reasoning...

thanks for the help, it is greatly appreciated!