Julian Date Help 1

[mdctsg]

Hello

I am trying to convert my normal date 03/29/2005 into a Julian date format 05088. My db is populated through a text import. My table is table1, my field is Date and my Query is Query1. For the test purpose. I am having a horrible time with this. Can anyone help me please.

Thanks
Bob

 

[Remou]

Hi
This is a section from an old MSDN library. Hope it is of use to you.
' *********************************************************************
' FUNCTION: CDate2Julian()
'
' PURPOSE: Convert a date to a Julian day. The function works with
' dates based on the Gregorian (modern) calendar.
'
' ARGUMENTS:
' MyDate: A valid Microsoft Access date.
'
' RETURNS: A three digit Julian day as a string.
' *********************************************************************
Function CDate2Julian(MyDate As Date) As String
CDate2Julian = Format(MyDate - DateSerial(Year(MyDate) - 1, 12, _
31), "000")
End Function

To test this function, type the following line in the Debug window, and then press ENTER:

?CDate2Julian(#3/1/1996#)

Note that Julian day 061 is returned.

To convert a valid Microsoft Access date to a Julian day in a format like 96061, type the following line in the Debug window, and then press ENTER:

?Year(#3/1/1996#) Mod 100 & CDate2Julian(#3/1/1996#)

Note that 96061 is returned.

 

[PHV]

A starting point (without UDF):
JulDate: Format(1000*Format(table1.Date,"yy")+Format(table1.Date,"y"),"00000")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mdctsg]

Remou I tried that sample from MS but it didn't seem to work the way I wanted. Thanks very much for your input.


PHV that worked great. Just what I needed. If I can impose on either of you again. I have been informed that I need to add UP to the front of the date. So it should read UP02088 for today. Why I do not know but can you shed an ideal on this.

Thanks
Bob

 

[mdctsg]

Ok I think I have it figured out. I will need to find out where they are getting the letter they want at the end but as far as the UP goes I can add it to the front of the date using PHV's code. I added a table called Mark to give me the letter at the end until I get their data

SELECT Table1.date AS Expr1, Format(1000*Format([table1].[Date],"+yy")+Format([table1].[Date],"y"),"""UP""00000")+Mark AS Date1
FROM Table1;

That seems to be giving me what I need for now.

Thanks again
Bob