Results 1 to 14 of 14
  1. #1
    JonReedTDC is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2016
    Posts
    8

    Julian Dates from AS400 into Access

    I have started building a query and have come across an issue. The tables are linked to an IBMi AS400 database. All of the dates are currently pulling in as Julian dates. Example:



    4/14/16 comes in from AS400 as 2016105
    4/15/16 comes in from AS400 as 2016106

    I am needing to convert this number string 2016106 to the more common date format (being 04/15/16).

    Any ideas how this can be done for the query I am building?

    Thanks!

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Let's say that your field name is "JulianDate". Here is a calculation that will convert that to a date.
    Code:
    DateSerial(Left([JulianDate],4),1,1)+Mid([JulianDate],5,3)-1

  3. #3
    JonReedTDC is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2016
    Posts
    8
    That is straight up magic.... Thanks!

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You are welcome!

    Since Julian Date is really the year, and then the day number of the year, I just took the year and made the date January 1st of the year, added the number of days, and subtracted 1.

  5. #5
    JonReedTDC is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2016
    Posts
    8
    So... Ran into a little issue with the above formula to change Julian to Normal Date.

    When I set a criteria like ">2/5/16" I get an error "Data type mismatch in criteria expression".

    I have the Format for the field set to "Short Date" however that did not make a difference.

    Suggestion???

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    If this is a date/time type, dates need delimiters.
    Did you try
    ">#2/5/16#" or ">#2/5/2016#" (without the quotes)

  7. #7
    JonReedTDC is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2016
    Posts
    8
    Even when I type use >#2/5/2016# I get the "Data type mismatch in criteria expression" error.

    Here are my format choices for the field.

    Click image for larger version. 

Name:	SH1.png 
Views:	10 
Size:	14.3 KB 
ID:	24402

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Don't understand why you are concerned with formatting in a table......

    I thought you were using a query. I created a query
    Code:
    SELECT test.JulianDate, DateSerial(Left([JulianDate],4),1,1)+Mid([JulianDate],5,3)-1 AS CalDate
    FROM test
    WHERE (((DateSerial(Left([JulianDate],4),1,1)+Mid([JulianDate],5,3)-1)>#4/15/2016#));
    and did not get a type mismatch error.

    Maybe I'm missing something......???

  9. #9
    JonReedTDC is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2016
    Posts
    8
    Here is my Code

    SELECT IESFILE_ORDERI.[ORODR#] AS OrderNum, DateSerial(Left([ORECDT],4),1,1)+Mid([ORECDT],5,3)-1 AS EmptyDate, IESFILE_CUSTMAST.CUNAME AS CustName, IESFILE_ORDERI.OROSNM AS OrgCity, IESFILE_ORDERI.ORARA AS OrgArea, IESFILE_ORDERI.OROST AS OrgState, IESFILE_CUSTMAST_2.CUBZIP AS OrgZIP, IESFILE_ORDERI.ORDSNM AS DestCity, IESFILE_ORDERI.ORINAR AS DestArea, IESFILE_ORDERI.ORDST AS DestState, IESFILE_CUSTMAST_1.CUBZIP AS DestZIP, IESFILE_ORDERI.ORMILE AS DriverMiles, TotalMilesSum.SumOfDIEMIL AS EmptyMiles, TotalMilesSum.SumOfDITMIL AS TotalMiles, IIf([SumOfDITMIL]=0,0,[SumOfDIEMIL]/[SumOfDITMIL]) AS PercDH, IIf([BIRATE]=0,IIf([ORMILE]=0,0,[ORBAML]/[ORMILE]),[BIRATE]/100) AS Rate, IESFILE_ORDBILL.ORBAML AS OrdBillAmount, IESFILE_ORDBILL.ORFSC AS FuelSurcharge, IESFILE_ORDBILL.ORBAMT AS TotalAmount
    FROM IESFILE_CUSTMAST AS IESFILE_CUSTMAST_2 INNER JOIN (IESFILE_CUSTMAST AS IESFILE_CUSTMAST_1 INNER JOIN (IESFILE_CUSTMAST INNER JOIN (IESFILE_BILLING INNER JOIN (TotalMilesSum INNER JOIN (IESFILE_ORDBILL INNER JOIN IESFILE_ORDERI ON IESFILE_ORDBILL.[ORODR#] = IESFILE_ORDERI.[ORODR#]) ON TotalMilesSum.[DIODR#] = IESFILE_ORDERI.[ORODR#]) ON IESFILE_BILLING.BIODR = IESFILE_ORDERI.[ORODR#]) ON IESFILE_CUSTMAST.CUCODE = IESFILE_ORDERI.ORBILL) ON IESFILE_CUSTMAST_1.CUCODE = IESFILE_ORDERI.ORCONS) ON IESFILE_CUSTMAST_2.CUCODE = IESFILE_ORDERI.ORLDAT
    WHERE (((DateSerial(Left([ORECDT],4),1,1)+Mid([ORECDT],5,3)-1)>#3/1/2016#) AND ((IESFILE_BILLING.BIAMT)=[ORBAML]))
    ORDER BY IESFILE_ORDERI.[ORODR#] DESC;

    And it gets me the "Data type mismatch in criteria expression" error.

  10. #10
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The SQL looks good to me.

    Only thing I can suggest is to start by removing all criteria and executing the query.
    If records are returned, change the WHERE clause to
    Code:
    WHERE DateSerial(Left([ORECDT],4),1,1)+Mid([ORECDT],5,3)-1)>#3/1/2016#
    and execute the query.


    If records are returned, then add the 2nd criteria
    Code:
    WHERE DateSerial(Left([ORECDT],4),1,1)+Mid([ORECDT],5,3)-1)>#3/1/2016# AND IESFILE_BILLING.BIAMT=[ORBAML]
    This is to determine which of the criteria is causing the problem.



    BTW, you have a field name "ORODR#".
    Shouldn't use special characters in object names. And the hash mark (#) is a date delimiter.

  11. #11
    JonReedTDC is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2016
    Posts
    8
    Thanks Steve!

    In regards to the pound symbol... Our AS400 field is actually named that so I can not make the change. For some reason they set up field names in our database system with special characters such as #, @, and $.... No idea why

    Also... When I use just the below Where clause I will get the Data type mismatch in criteria expression error message... I am almost out of hope of why this date range can't have a criteria on it.

    WHERE DateSerial(Left([ORECDT],4),1,1)+Mid([ORECDT],5,3)-1)>#3/1/2016#

  12. #12
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Looks like you have an extra parenthesis at the end of your statement. Try adding in another at the beginning so they balance correctly.
    Code:
    WHERE (DateSerial(Left([ORECDT],4),1,1)+Mid([ORECDT],5,3)-1)
    Also, do you have any blanks, nulls, or anything else in [ORECDT] that cannot be converted to a Date?
    One simple way to check is to temporarily remove the criteria, and then add this calculated field:
    Code:
    Test: DateSerial(Left([ORECDT],4),1,1)+Mid([ORECDT],5,3)-1
    and sort by it, and check your values at the top and bottom to make sure that they are all valid and you do not have any errors.

  13. #13
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Ack!!


    Apparently I need to learn how to count again.
    I counted the parentheses.... twice!!

    Good catch Joe.
    I am soooo embarrassed.

  14. #14
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    No worries Steve!

    As the kids say, "BTDTBTTS" ("Been there, done that, bought the t-shirt!")

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Current Date to Julian Date?
    By Hammilton in forum Forms
    Replies: 12
    Last Post: 12-11-2015, 09:27 PM
  2. Replies: 8
    Last Post: 12-02-2013, 03:46 PM
  3. Julian (ordinal) date one day off
    By gregu710 in forum Access
    Replies: 6
    Last Post: 02-02-2012, 06:21 PM
  4. Julian Date Function Help Please
    By campanellisj in forum Programming
    Replies: 3
    Last Post: 05-13-2011, 12:59 PM
  5. Replies: 3
    Last Post: 11-19-2009, 09:15 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums