Can you use a Select Statement within a IIF

[Jer]

I am pushing my Access Query abilities here. I am wanting to know if I can
replace the 'truepart' of my iif statement here with a select statement:

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

I would like to replace the '1' with a select statment that tries to find
the job number that come up after the Arrive Plant time where the job number
shows up in the 'status' column as well. I believe it would be something
like this:

Select x_logs.status,
From x_logs
Where x-logs.time>'current time'

I hope I explained this well enough. Please let me know if this is possible
and if so how the syntax should be structured.

Thank you in advance!

 

[Ofer Cohen]

You can use dlookup

IIf([status]="Arrive Plant",Dlookup("FieldName","TableName","Criteria"),2)
AS [Job #]

 

[Jer]

Ok. That seems like an interesting solution. I am attempting to run with it
and have modified my query to the following:

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

But where I have put the ? I am unsure how to write the criteria. I want it
to find the next record with a job number in the status field, and I am
figuring it will know its the next one because the time with be greater. Am
I on the right track?

Please help a little more.

Thanks much!

You can use dlookup

IIf([status]="Arrive Plant",Dlookup("FieldName","TableName","Criteria"),2)
AS [Job #]

--
Good Luck
BS"D

I am pushing my Access Query abilities here. I am wanting to know if I can
replace the 'truepart' of my iif statement here with a select statement:

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

I would like to replace the '1' with a select statment that tries to find
the job number that come up after the Arrive Plant time where the job number
shows up in the 'status' column as well. I believe it would be something
like this:

Select x_logs.status,
From x_logs
Where x-logs.time>'current time'

I hope I explained this well enough. Please let me know if this is possible
and if so how the syntax should be structured.

Thank you in advance!