Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2014
    Posts
    1

    Unanswered: Need to convert SQL into Access query

    Hello everyone, need some help. I know how to do it in SQL TOAD but need to do in Access.

    SELECT
    xref.IVXRT AS SUB_TYPE,
    MMSDM.CONVERT_JDE_DATE(xref.IVEFTJ) AS EFFECTIVE_DATE,
    MMSDM.CONVERT_JDE_DATE(xref.IVEXDJ) AS EXP_DATE,
    xref.IVITM AS ORG_ITM,
    i.DESCRIPTION as ORG_DESC,
    i.ITEM_DISCONTINUED as ORG_DISCONTINUED,
    i.STATUS as ORG_STATUS,
    i.ATOMIC_UOM as ORG_AUOM,
    i.ATOMIC_CONV as ORG_AUOM_CONV,
    i.SALES_GROUPING as GROUPING,
    i.SALES_GROUPING_DESC as GROUP_DESC,[/INDENT]
    CASE WHEN I.SALES_GROUPING in ('1234') THEN 'PRIVATE_LABEL' ELSE 'SUBSTITUTE' END AS SUBSTITUTE_TYPE,

    xref.IVCITM AS SUB_ITM,
    i2.DESCRIPTION as SUB_DESC,
    i2.ITEM_DISCONTINUED as SUB_DISCONTINUED,
    i2.STATUS as SUB_STATUS,
    i2.ATOMIC_UOM as SUB_AUOM,
    i2.ATOMIC_CONV as SUB_AUOM_CONV,
    E1JRNDATE AS DATE_JOURNALED,
    xref.IVURCD AS Gate_Flag

    FROM MMSDM910.SRC_E1_MMS_F4104 xref
    Left outer join mgmsh.item i on xref.ivitm = i.ITEM_NO
    left outer join mgmsh.item i2 on xref.ivcitm = i2.item_no

    WHERE MMSDM.CONVERT_JDE_DATE(xref.IVEXDJ) > sysdate

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Join syntax in Access is odd its tricky to explain but either google it or use the query designer.

    Replace the case with iif.

    I dont know what convert_jde_date does but access does have its own rich date time functions.

    This does assume you are using plain vanilla access and not connecting to a server backend
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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