Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Julian Dates (2000)

    I would like to have the user enter the Julian date and have that number saved in the table field, but then have another text box on a form and/or report that shows a regular date.
    Thanks,
    Deb

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Julian Dates (2000)

    Say that the user enter the Julian date number (I assume that is what you mean) in a text box named txtJulian. You can use the following expression in the control source of another text box:

    =[txtJulian] - 2415020

    and format it as a date; alternatively, you can create the following custom function in a standard module:

    Function JDN2Date(JDN As Long) As Date
    JDN2Date = JDN - 2415020
    End Function

    Then, you can use

    =JDN2Date([txtJulian])

  3. #3
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Julian Dates (2000)

    Hans,
    I tried both options and I couldn't get them to work, the first method gives me 12/31/1899 every time. And I'm getting an error message using the second option. This is what I did. I created a txtJulianDate field in my table. I added it to my form and then added an unbound text box and added the [txtJulianDate]-2415020 formula. (BTW, where do you get the 2415020 number). The second option, I went to modules, clicked new and typed in what you have and on my form added an unbound text box with the =JDN2Date([txtJulian]). I get an error message. #Error in my text box. In your function you have JDN2Date=JDN-2415020 and that's the line that's highlighted yellow when i try and run it. What is JDN. I'm not very VBA proficient, but am learning. Thanks.
    Deb

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Julian Dates (2000)

    I got the constant 2415020 by googling; it is the Julian date number for "day 0" of the date system used by Access.

    JDN is just the name of the argument in the function; when the function is run, it will be replaced by the value you pass to the function.

    If you named your field txtJulianDate, you should use that name in the expressions instead of txtJulian. What kind of field is txtJulianField (text, date, number), and can you give an example of what you enter in this field?

  5. #5
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Julian Dates (2000)

    Hans,
    txtJulianDate is a Text field. And I did make sure I had the right names in all areas the same. I type in 251, which is supposed to be September 8th, 2003. Maybe I'm using the wrong Julian date. I'm going off a government calendar, which had the regular date and then the julian number underneath it.
    Deb

  6. #6
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Julian Dates (2000)

    Hans,
    I found this article on the MS knowledge dbase. #209922. But I think my problem is I'm not putting in the write place and I'm not sure how to call it.
    Thanks,
    Deb

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Julian Dates (2000)

    Aha, communication failure. You and I were writing about different varieties of Julian date. I was thinking about a system, used among others by astronomers, in which the Julian date is the number of days since noon, Universal Time on January 1, 4713 BCE. You meant the day number within the current year. Try this:

    =DateAdd("d", [txtJulianDate], DateSerial(Year(Date()), 1, 0))

    Or, if you want a function:

    Function JDN2Date(JDN As Long) As Date
    JDN2Date = DateAdd("d", JDN, DateSerial(Year(Date()), 1, 0))
    End Function

    Then, you can use

    =JDN2Date([txtJulianDate])

    I hope this will work for you. If not, post back. BTW, why not make the field in the table into a number field?

  8. #8
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Julian Dates (2000)

    Hans,
    That worked and it gives me what I need. I have another question. The database I have already has a dtmDateWorked field and we have about 8000 records. We're a gov. site so on all their paperwork they use the Julian number, so after discussing things with the data entry people, it would be easier for them to just enter the Julian #, my question is, how can I convert all the regular dates I currently have in my table to Julian. Or can I type in the Julian # using the formula you gave me on a form and then update a field in the table to store the date?

    Thanks,
    Deb

  9. #9
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Julian Dates (2000)

    Hans,
    Another thought, I have dates that span the last 4 years. Does this formula only work for the current year? Once I convert all my old data, would that be a problem?
    Thanks,
    Deb

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Julian Dates (2000)

    I don't think you would want to store the Julian date in the database, unless you also store the year in a separate field. If you only have the Julian date, you wouldn't know whether 38 means 2/7/2000 or 2/7/2001 etc.

    The formula I gave you always returns a date in the current year; this is convenient for data entry, but not for displaying historical records.

  11. #11
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Julian Dates (2000)

    Ok, that's what I thought and just figured out while playing. So my next question is, can i have an unbound JulianDate field on a form, and an unbound calculated field with the formula you gave me and then have that date update a table dateworked field? I'm having trouble figuring that out and I'm not very good with code.
    Thanks,
    Deb

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Julian Dates (2000)

    If the DateWorked field is in the record source of the form, it's fairly easy:
    <UL><LI>Open the form in design view.
    <LI>Select the txtJulianDate text box (the unbound control.)
    <LI>Activate the Properties window (View | Properties.)
    <LI>Activate the Event tab.
    <LI>Click in the After Update event.
    <LI>Select "Event Procedure" from the dropdown list.
    <LI>Click the Builder button (the three dots ... to the right of the dropdown arrow.)
    <LI>You will be taken to the Visual Basic Editor. Complete the event procedure so that it looks as follows:

    Private Sub txtJulianDate_AfterUpdate()
    Me.DateWorked = DateAdd("d", Me.txtJulianDate, DateSerial(Year(Date), 1, 0))
    End Sub

    <LI>Switch back to Access (Alt+F11 or use the Windows task bar.)
    <LI>Close and save the form.[/list]That's it. Substitute the actual names where necessary.

  13. #13
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Julian Dates (2000)

    Hans, as usual you ARE DA MAN. If you were here and wouldn't sue me for sexual harassment I'd plant a big wet one on ya.
    Thanks a TON.
    Deb

Posting Permissions

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