Can you use a Select Statement within a IIF

J

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!
 
O

Ofer Cohen

You can use dlookup

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

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!

Ofer Cohen said:
You can use dlookup

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

--
Good Luck
BS"D


Jer said:
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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top