Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2012
    Posts
    1

    Unanswered: Access Query return two forward slashes in Date Format

    Hello All,
    I need help with this Access query I am running . I am using Access 2010. When the query is ran it return a date format with two forward slashes like 1//12. Should be 01/12. It only occur on single number months. The Months of Oct-Dec are not affected. The date field for this data has the following: OR Month: (Left(Nz([ORIGIN_RECEIPT_DT],[FACT_GOODS_AT_CONSOL_DT]),2)) & "/" & (Right(Nz([ORIGIN_RECEIPT_DT],[FACT_GOODS_AT_CONSOL_DT]),2))
    I inherited this query. I tried removing the /. When I do it correct the double forward slash issues, but it remove the forward slash from months with two digits. I want the data to return single date format as 01/12, 02/12, 03/12..
    I would greatly appreciate it any help in correct this problem. Thanks in advance

    Here is the SQL code:
    SELECT (Left(Nz([ORIGIN_RECEIPT_DT],[FACT_GOODS_AT_CONSOL_DT]),2)) & "/" & (Right(Nz([ORIGIN_RECEIPT_DT],[FACT_GOODS_AT_CONSOL_DT]),2)) AS [OR Month], GFP_FACTORY.FCTY_CNTRY_CD AS MCO, GFP_FACTORY.FACTORY_PORT AS PORT, IIf([DEST_REGN_CD]="AF","EU",[DEST_REGN_CD]) AS [Dest Rgn], GFP_PO_LINE_HIST_V.DEST_CNTRY_CD AS [Dest Ctry], GFP_PO_LINE_HIST_V.BUY_GROUP_DESC AS [Buy Group], GFP_PO_LINE_HIST_V.QUALITY_DESC AS Quality, GFP_PO_LINE_HIST_V.MODE_CD AS Mode, Sum(GFP_PO_LINE_HIST_V.PO_ITEM_QTY) AS Qty
    FROM GFP_PO_LINE_HIST_V INNER JOIN GFP_FACTORY ON GFP_PO_LINE_HIST_V.FACTORY_CD = GFP_FACTORY.FCTY_CD
    WHERE (((Nz([GFP_PO_LINE_HIST_V]![ORIGIN_RECEIPT_DT],[GFP_PO_LINE_HIST_V]![FACT_GOODS_AT_CONSOL_DT]))>#6/1/2012#) AND ((GFP_PO_LINE_HIST_V.PO_STATUS_CD) Is Null))
    GROUP BY (Left(Nz([ORIGIN_RECEIPT_DT],[FACT_GOODS_AT_CONSOL_DT]),2)) & "/" & (Right(Nz([ORIGIN_RECEIPT_DT],[FACT_GOODS_AT_CONSOL_DT]),2)), GFP_FACTORY.FCTY_CNTRY_CD, GFP_FACTORY.FCTY_CD, GFP_FACTORY.FACTORY_PORT, IIf([DEST_REGN_CD]="AF","EU",[DEST_REGN_CD]), GFP_PO_LINE_HIST_V.DEST_CNTRY_CD, GFP_PO_LINE_HIST_V.BUY_GROUP_DESC, GFP_PO_LINE_HIST_V.QUALITY_DESC, GFP_PO_LINE_HIST_V.MODE_CD
    HAVING (((GFP_FACTORY.FCTY_CNTRY_CD)<>"ARGE" And (GFP_FACTORY.FCTY_CNTRY_CD)<>"BRAZ") AND ((GFP_FACTORY.FCTY_CD)="IA") AND ((GFP_PO_LINE_HIST_V.BUY_GROUP_DESC)<>"SALESMAN SAMPLE") AND ((GFP_PO_LINE_HIST_V.MODE_CD)<>"TR"));

  2. #2
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    It's probably best to use the format$ function rather than trying to parse the date. This assumes ORIGIN_RECEIPT_DT and FACT_GOODS_AT_CONSOL_DT are both typed as date fields:

    SELECT Format$(Nz([ORIGIN_RECEIPT_DT],[FACT_GOODS_AT_CONSOL_DT]),"mm/yy") AS [OR Month]
    ...
    GROUP BY Format$(Nz([ORIGIN_RECEIPT_DT],[FACT_GOODS_AT_CONSOL_DT]),"mm/yy")
    ...

Posting Permissions

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