Date to Julian date format

M

mtate

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

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
 
S

Stuart McCall

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.

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
 
M

Marshall Barton

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.


Format(dt, "\1yyy")
 
J

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

mtate

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

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

Marshall Barton

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.


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

Stuart McCall

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.

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
 
J

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

Marshall Barton

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.


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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top