Julian Date to Short Form

[Mark P]

I am trying to convert julian dates (year and day of year, ex: 2007/231) to
normal short form dates in a form. What is the best way to do this? Does
Access recognize Julian date and convert it automatically, like it does when
you enter Jan 1 2008 and (when set to short form) it changes to 1/1/2008?

Thanks.

 

[Douglas J. Steele]

No, Access doesn't recognize that date format.

What you can do is write a function to translate for you, something like the
following untested aircode:

Function JulianToDate(JulianDate As String) As Date
Dim varParts As Variant

varParts = Split(JulianDate, "/")
If UBound(varParts) = 1 Then
JulianToDate = DateAdd("d", varParts(1), _
DateSerial(varParts(0) - 1, 12, 31))
End If

End Function

What I'm doing is using the Split function to break your string into two
parts. varParts(0) should contain the year, and varParts(1) should contain
the days. Add the number of days to Dec 31 of the previous year, and that
should give you the correct date.

 

[Klatuu]

And to complete the loop:

Function DateToJulian(GregorianDate As Date) As String
DateToJulian = Format(GregorianDate, "yyyy\/") & _
CStr(DateDiff("d", DateSerial(Year(GregorianDate) - 1, 12, 31),
GregorianDate))
End Function

 

[John W. Vinson]

I am trying to convert julian dates (year and day of year, ex: 2007/231) to
normal short form dates in a form. What is the best way to do this? Does
Access recognize Julian date and convert it automatically, like it does when
you enter Jan 1 2008 and (when set to short form) it changes to 1/1/2008?

Thanks.

What's the context? Does a user type this date into a textbox?

If you have an unbound textbox for this Julian date and another textbox bound
to a datetime field, you could use code in the unbound textbox's afterupdate
event:

Private Sub txtJDate_AfterUpdate()
Me!txtRealdate = DateSerial(Left(Me!txtJDate, 4), 1, Right(Me!txtJDate, 3))
End Sub

You can also display the Julian date for an existing date value in the form's
Current event:

Me!txtJDate = Format([datefield], "yyyy\/y")