how to use a created column data in a where statement

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Akilah - 15 Mar 2006 21:48 GMT

I want to use column created in the select statement in the where statement
like this:

SELECT tbl_RAP_Award.PO_Number, tbl_RAP_Award.Inspection_Id,
tbl_RAP_Award.Property_ID, tbl_RAP_Award.PIH_Project_Number,
tbl_RAP_Award.Inspector_Id, tbl_RAP_Award.Scheduled_Inspection_Date,
tbl_RAP_Award.Upload_Date, tbl_RAP_Award.Scheduler_Inspection_Status_Code,
tbl_RAP_Award.AuctionAwardDate, tbl_RAP_Award.PO_Start_Date,
tbl_RAP_Award.PO_End_Date, tbl_RAP_Award.PO_Rev_End_Date,
iif(tbl_RAP_Award.PO_Rev_End_Date is not null,
tbl_RAP_Award.PO_Rev_End_Date,tbl_RAP_Award.PO_End_Date) AS
Current_PO_End_Date, tbl_RAP_Award.Reverse_Auction_Number,
tbl_RAP_Award.Bidder_Id, tbl_RAP_Award.Name, t_contractors.email,
tbl_RAP_Award.Payment_Status

FROM t_contractors INNER JOIN tbl_RAP_Award ON t_contractors.id =
tbl_RAP_Award.Bidder_Id

WHERE (tbl_RAP_Award.Scheduled_Inspection_Date Is Null and
tbl_RAP_Award.Payment_Status<>'Verified Unsuccessful' and (date() >
Current_PO_End_Date ))

How can I do this?

Thanks

Reply to this Message

John Spencer - 15 Mar 2006 22:25 GMT

In Access you can only do it by repeating the entire calculation
SELECT tbl_RAP_Award.PO_Number, tbl_RAP_Award.Inspection_Id,
tbl_RAP_Award.Property_ID, tbl_RAP_Award.PIH_Project_Number,
tbl_RAP_Award.Inspector_Id, tbl_RAP_Award.Scheduled_Inspection_Date,
tbl_RAP_Award.Upload_Date, tbl_RAP_Award.Scheduler_Inspection_Status_Code,
tbl_RAP_Award.AuctionAwardDate, tbl_RAP_Award.PO_Start_Date,
tbl_RAP_Award.PO_End_Date, tbl_RAP_Award.PO_Rev_End_Date,
iif(tbl_RAP_Award.PO_Rev_End_Date is not null,
tbl_RAP_Award.PO_Rev_End_Date,tbl_RAP_Award.PO_End_Date) AS
Current_PO_End_Date, tbl_RAP_Award.Reverse_Auction_Number,
tbl_RAP_Award.Bidder_Id, tbl_RAP_Award.Name, t_contractors.email,
tbl_RAP_Award.Payment_Status

FROM t_contractors INNER JOIN tbl_RAP_Award ON t_contractors.id =
tbl_RAP_Award.Bidder_Id

WHERE (tbl_RAP_Award.Scheduled_Inspection_Date Is Null and
tbl_RAP_Award.Payment_Status<>'Verified Unsuccessful' and (date() >
iif(tbl_RAP_Award.PO_Rev_End_Date is not null,
tbl_RAP_Award.PO_Rev_End_Date,tbl_RAP_Award.PO_End_Date) ))

That could be shortened using the NZ function.
NZ(tbl_RAP_Award.PO_Rev_End_Date, tbl_RAP_Award.PO_End_Date)

>I want to use column created in the select statement in the where statement
> like this:
[quoted text clipped - 21 lines]
>
> Thanks

Reply to this Message