Results 1 to 7 of 7
  1. #1
    gregu710 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    Frederick, CO (near Longmont and Boulder)
    Posts
    40

    Julian (ordinal) date one day off


    Hi, I have a VB Script that converts the current date into an ordinal date code, i.e. 001-365 for the day of the year, and a 2-digit number for the last 2 digits of the current year. Only problem is that the number is one digit off of what it should be. That is, if Feb. 1, 2012 should be 03212 (the 32nd day of 2012), the output is actually 03312. Here is what the script looks like that creates.

    Public Function CDate2Julian(MyDate As Date) As String
    CDate2Julian = Format(MyDate - DateSerial(Year(MyDate) - 1, 12, _
    31), "000") & Year(MyDate) Mod 100
    End Function

    I've found that if I change the 31 at the end of that string to 32, it's good for the first 4 months, then I have to change back some time between April 30 and May 15(ish) to get the rest of the year to calculate right. Not sure if it's a Leap Year thing or not....

  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    This might help you. Not sure.

    http://www.wilsonmar.com/datepgms.htm#JulianDatez

    Alan

  3. #3
    gregu710 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    Frederick, CO (near Longmont and Boulder)
    Posts
    40
    That' seems to be geared more toward real Julian Dates. To my knowledge, Ordinal dates are just based on the number of days in a specific year. I've seen it often in manufacturing where I work as a shortened way of applying a date code to a product.

  4. #4
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    I changed your formula slightly in an expression in a query and it appears to return the correct values. I tested manually against dates out through May

    Expr1: Format([Mydates]-DateSerial(Year([Mydates])-1,12,31),"000") & Format(Right(Year([Mydates]),2),"00")

    Alan

  5. #5
    gregu710 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    Frederick, CO (near Longmont and Boulder)
    Posts
    40
    Alan, it does work, BUT, there is a problem. If I use the Date Picker calendar, it always attaches the time to the date as well, even though I have short Date format selected. IF I go in and manually type in a date without any time, it functions as desired. Any ideas how to turn off the stupid time? I suspect that is the original problem with my CDate2Julian script as well.

  6. #6
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    I don't know how to turn off the Date picker in AC2010. Prior to AC 2007, I used Allen Browne's Date Picker. Here is a link to it. Perhaps you can find a way to substitute AB's for the Access'

    http://allenbrowne.com/ser-51.html

    Just did a google search on removing date picker. Look here

    http://www.techrepublic.com/blog/mso...date-field/556
    Last edited by alansidman; 02-02-2012 at 06:04 PM. Reason: found Date Picker Removal

  7. #7
    gregu710 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    Frederick, CO (near Longmont and Boulder)
    Posts
    40
    Alan, I think I've got it. The problem, I believe, is that I had the default values for the date fields set to =Now(), which gives you an EXACT date/time, instead of havnig it set to "=Date(), which gives you just the date. It seems to be functioning now. Thanks for taking the time to help out!

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

Similar Threads

  1. Replies: 12
    Last Post: 04-26-2012, 04:01 AM
  2. Replies: 1
    Last Post: 12-12-2011, 06:32 AM
  3. Ordinal Number Format
    By injanib in forum Forms
    Replies: 2
    Last Post: 06-15-2011, 01:55 PM
  4. Julian Date Function Help Please
    By campanellisj in forum Programming
    Replies: 3
    Last Post: 05-13-2011, 12:59 PM
  5. Replies: 1
    Last Post: 07-07-2010, 04:22 PM

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