Is there a better way than using these IIF's?

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

Does anyone want to recommend a better method for displaying this
Amount_Booked field based on the date criteria. I'm feeling like
using these IIF's is probably a very novice solution (as I am very
much a novice :)

SELECT dbo_ProjectBooking.ProjectBooking_ID,
dbo_ProjectBooking.Project_ID, dbo_ProjectBooking.Line_No,
dbo_ProjectBooking.Date_Booked, dbo_ProjectBooking.Amount_Booked,
IIf([Date_Booked] Between #1/1/2003# And
#12/31/2003#,[Amount_Booked],"") AS [2003 Amount], IIf([Date_Booked]
Between #1/1/2004# And #12/31/2004#,[Amount_Booked],"") AS [2004
Amount], dbo_ProjectBooking.Customer_ID,
dbo_ProjectBooking.Purchase_Order, dbo_ProjectBooking.Market_ID
FROM dbo_ProjectBooking;
 
Does anyone want to recommend a better method for displaying this
Amount_Booked field based on the date criteria. I'm feeling like
using these IIF's is probably a very novice solution (as I am very
much a novice :)

SELECT dbo_ProjectBooking.ProjectBooking_ID,
dbo_ProjectBooking.Project_ID, dbo_ProjectBooking.Line_No,
dbo_ProjectBooking.Date_Booked, dbo_ProjectBooking.Amount_Booked,
IIf([Date_Booked] Between #1/1/2003# And
#12/31/2003#,[Amount_Booked],"") AS [2003 Amount], IIf([Date_Booked]
Between #1/1/2004# And #12/31/2004#,[Amount_Booked],"") AS [2004
Amount], dbo_ProjectBooking.Customer_ID,
dbo_ProjectBooking.Purchase_Order, dbo_ProjectBooking.Market_ID
FROM dbo_ProjectBooking;
Click to expand...

You might try a Crosstab Query using Year([DateBooked]) as the column
header.

John W. Vinson[MVP]
 
Back
Top