Unanswered: I need help creating a query to export to an Excel workbook
I need help in creating a query to export to an Excel workbook.
I have a master list of projects with the common ID (Proj Number). In this file, I have 5 forecast items and dates (see sample below). My goal is to export the forecast dates that are less than today. I am using the formula <=Now(), which seems to work in independently queries. But, I cannot use that formula in all 5 forecast dates because it does not return any data. I am probably using the <=Now() formula incorrectly. This is my first attempt in creating a query like this.
Sample Excel Output:
ProjNumber PR001 FC PR002 FC PR003 FC PR004 FC PR005 FC
PR556978 3/1/15 3/30/15 4/2/15 2/2/15 3/27/15
I need to see past due dates from all 5 of the forecast items (PR001 FC, PR002 FC, PR003 FC, PR004 FC, PR005 FC).
First problem is probably that you are using Now() which returns both date and time. Try using Date() instead. The problem may be more than that but if you provide the SQL for the query you are using it would be easier to give you advice. Some sample data to check against would be helpful as well.
Hi rodrich, thank you for replying. Yes, I cought that and change the Now to Date(). Thank you.
Here is the sql code. I run this query and no data is returned.
SELECT tbl_EE_Report.ID, tbl_EE_Report.[Proj Number], tbl_EE_Report.[Order Number OM] AS [Order Number], tbl_EE_Report.[Program Type of Order OM] AS [Program Type], tbl_EE_Report.[Order Status OM], tbl_EE_Report.[Site Required Date for Order OM] AS SRD, tbl_EE_Report.[Item Number OM], tbl_EE_Report.[Item Description OM], tbl_EE_Report.[Qty for Order OM] AS [Qty of Order], tbl_EE_Report.[Item Status Flow OM], tbl_EE_Report.[Promise Date for Order Line OM] AS [Promise Date], tbl_EE_Report.[Display Line Status OM], tbl_EE_Report.CI025_FORECAST, tbl_EE_Report.CI025_ACTUAL, tbl_EE_Report.[Max CED of Order], tbl_EE_Report.[Callout Criteria Met]
FROM tbl_EE_Report INNER JOIN tbl_MASTER_2015_POE_Locked ON tbl_EE_Report.[Proj Number] = tbl_MASTER_2015_POE_Locked.PA_NUMBER
WHERE (((tbl_EE_Report.[Order Status OM])="Forecasted" Or (tbl_EE_Report.[Order Status OM])="Partially Called Out" Or (tbl_EE_Report.[Order Status OM])="Pending Approval") AND ((tbl_EE_Report.[Qty for Order OM])<>"0") AND ((tbl_EE_Report.[Item Status Flow OM])="AWAITING_SHIPPING") AND ((tbl_EE_Report.[Display Line Status OM])="Awaiting Shipping") AND ((tbl_EE_Report.CI025_FORECAST)<=Date()-1) AND ((tbl_EE_Report.CI025_ACTUAL) Is Null))
ORDER BY tbl_EE_Report.CI025_FORECAST;