How to use Dlookup or Dmax

[Guest]

I am working a field that is supposed to return the value from a subsequent
record.

This is what I have so far:

SELECT x_logs.vehicle_nu, x_logs.time, x_logs.date, x_logs.status,
IIf([status]="Arrive Plant",Dlookup("[status]","x_logs", ??????? ),2) AS
[Job #]
FROM x_logs
WHERE (((x_logs.date)="06/22/07"))
ORDER BY x_logs.vehicle_nu;

I need help writing the criteria please. I want the next value in the
'status' field where the left 5 characters are the word 'Badge'. Is there a
way to use the Left 5 command in the criteria of the dlookup?

Please advise.
Thanks in advance!

 

[John Spencer]

DLookup("Status","x_logs", "Status Like ""Badge*""")

The problem is that this will return the first value in the table that has
Badge as the beginning value of the status field.

How do you define the record the "Next" record? Is it one with a date
greater than a certain date and time for the specific vehicle.
Also are your date and time fields (not a good name) text fields or are they
DateTime fields.

PERHAPS (VERY BIG PERHAPS) the following may work to get you started.
SELECT _logs.vehicle_nu
, x_logs.time
, x_logs.date
, x_logs.status
, (SELECT First(Status)
FROM X_Logs as Xa
WHERE Xa.Date + Xa.Time =(
SELECT Min(X.Date = X.Time) as TheDateTime
FROM x_logs as X
WHERE x.Vehicle = x.Logs.Vehicle
AND x.Status Like "Badge*"
AND x.Date + x.Time > x_logs.Date + x_Logs.Time)) as NextStatus
FROM x_logs
WHERE x_logs.date=#06/22/07#

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..