Date to Julian date format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to be able to take a date format (mm/dd/yy) entered on an Access form
and convert it to a Julian date for an AS400 query. The file on the AS400 is
looking for this format: 1CYYDDD. For example, 10/15/07 would be 107288.

Can anyone help with this? I am using Access 2003.
 
I'm not quite sure about the "1C" at the front, since your example only has
a 1.

To get 07288 from 15 October, 2007, you can use the Format function with an
argument of "yyy":

?Format(#2007-10-15#, "yyy")
07288
 
mtate said:
I need to be able to take a date format (mm/dd/yy) entered on an Access
form
and convert it to a Julian date for an AS400 query. The file on the AS400
is
looking for this format: 1CYYDDD. For example, 10/15/07 would be 107288.

Can anyone help with this? I am using Access 2003.
Click to expand...

Paste this function into a standard module:

Public Function DateToJulian(NormalDate As Date) As String
Dim dYear As String
Dim jDay As String

dYear = Format(NormalDate, "yy")
jDay = Format(Str(NormalDate - DateValue("1/1/" & Str(dYear)) + 1),
"000")
DateToJulian = "1" & dYear & jDay
End Function

Then use it like this:

Debug.Print DateToJulian("10/15/07")

Result: 107288
 
mtate said:
I need to be able to take a date format (mm/dd/yy) entered on an Access form
and convert it to a Julian date for an AS400 query. The file on the AS400 is
looking for this format: 1CYYDDD. For example, 10/15/07 would be 107288.
Click to expand...


Format(dt, "\1yyy")
 
The other solutions posted will fail if the date occurs in the first 99 days
of the year.

Format(YourDate,"YY") & Format(DatePart("y",YourDate),"000")

Assuming that the 21st Century is 1 and the 20th Century is 0

C = Year(Date)\100 -19 will return the century so

Year(YourDate)\100 -19 & Format(YourDate,"YY") &
Format(DatePart("y",YourDate),"000")
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
OK. Glad Marshall's version (and Doug's) worked for you. BUT my test
using Jan 2, 2007 return 072 and not 07002.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
John said:
OK. Glad Marshall's version (and Doug's) worked for you. BUT my test
using Jan 2, 2007 return 072 and not 07002.
Click to expand...


Arrgghh, I never checked if the y format code returned
leading zeros. So much for quick answers :-(

Stuart, use John's expression if you want Jan 2 to come out
as 07002.
 
Marshall Barton said:
Arrgghh, I never checked if the y format code returned
leading zeros. So much for quick answers :-(

Stuart, use John's expression if you want Jan 2 to come out
as 07002.
Click to expand...

Arrgghh again. I've never had a need for julians myself, so I wrote the
function there and then (it looked simple enough). So much for ad-hoc
functions without thorough testing :-(

Thanks
 
AH HA! Now I know why two MVP's made this error.

You shouldn't be partying while trying to answer the question - a little
beer or wine can definitely change the accuracy of the answers. Of course,
sometimes a little beer or wine will generate a more creative solution.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
John said:
AH HA! Now I know why two MVP's made this error.

You shouldn't be partying while trying to answer the question - a little
beer or wine can definitely change the accuracy of the answers. Of course,
sometimes a little beer or wine will generate a more creative solution.
Click to expand...


And here I thought is was just a lack of sleep that was
responsible. Thanks for straightening me out on this subtle
issue, John.
 
Back
Top