Date to Julian date format - ADO DAO RDO RDS

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


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"mtate" <mtate@discussions.microsoft.com> wrote in message
news:ED897CE5-0702-413A-98DF-B3E1F61E2741@microsoft.com...
>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.

"mtate" <mtate@discussions.microsoft.com> wrote in message
news:ED897CE5-0702-413A-98DF-B3E1F61E2741@microsoft.com...
>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

mtate wrote:

>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")

--
Marsh
MVP [MS Access]

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

"mtate" <mtate@discussions.microsoft.com> wrote in message
news:ED897CE5-0702-413A-98DF-B3E1F61E2741@microsoft.com...
>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.

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

"Marshall Barton" wrote:

> mtate wrote:
>
> >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")
>
> --
> Marsh
> MVP [MS Access]
>

Glad to hear that it worked, but Doug said essentially the
same thing.
--
Marsh
MVP [MS Access]


mtate wrote:

>Thanks everyone for your response. I was able to use Marshall's post to get
>it to work.
>
>"Marshall Barton" wrote:
>
>> mtate wrote:
>>
>> >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")

Party pooper! <g>

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"John Spencer" <spencer@chpdm.edu> wrote in message
news:uu8WtzAEIHA.4476@TK2MSFTNGP06.phx.gbl...
>
> 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
> .
>
> "mtate" <mtate@discussions.microsoft.com> wrote in message
> news:ED897CE5-0702-413A-98DF-B3E1F61E2741@microsoft.com...
>>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.
>
>

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


mtate wrote:
> Thanks everyone for your response. I was able to use Marshall's post to get
> it to work.
>
> "Marshall Barton" wrote:
>
>> mtate wrote:
>>
>>> 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")
>>
>> --
>> Marsh
>> MVP [MS Access]
>>

John Spencer wrote:

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

--
Marsh
MVP [MS Access]