Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2008

    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).

    I am using Microsoft Access and Excel 2010.

    I appreciate any assistance I can get. Thank you.

  2. #2
    Join Date
    Mar 2015
    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.

  3. #3
    Join Date
    Sep 2008
    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;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts