how to use a created column data in a where statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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)
 
Back
Top