Date to Julian date format

[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.

 

[Douglas J. Steele]

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

 

[Stuart McCall]

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.

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

 

[Marshall Barton]

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.

Format(dt, "\1yyy")

 

[John Spencer]

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
..

 

[Guest]

Thanks everyone for your response. I was able to use Marshall's post to get
it to work.

 

[Marshall Barton]

Glad to hear that it worked, but Doug said essentially the
same thing.

 

[Douglas J. Steele]

Party pooper! <g>

 

[John Spencer]

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
'====================================================

 

[Marshall Barton]

OK. Glad Marshall's version (and Doug's) worked for you. BUT my test
using Jan 2, 2007 return 072 and not 07002.

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.

 

[Stuart McCall]

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.

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

 

[John Spencer]

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
..

 

[Marshall Barton]

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.

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

 

[Douglas J. Steele]

P.S. Do you have an empirically derived graph of the Bugs
per Beer ratio?

http://xkcd.com/323/