472,782 Members | 1,133 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,782 software developers and data experts.

Converting Gregorian date to Julian date (Access 2003)

I know that there is a thread about this, but I was unable to add to it; I just joined. The problem is that I do not want to change the date format for the entire database output, merely for a header on a report and for a field on that report.

I thought that I could just go into the expression builder and paste one of the codes that were posted in the thread about this conversion-you'll know that did not work.

So, I went to the new module and tried to enter the following information into the Immediate Window, but it did not work.

? Right(Year(#1/20/2003#),1) &

I followed the direction to enable the 'Require Variable Declaration' in the 'Tools' menu of the Visual Basic Editor and then copied the following into the 'Procedure Window':

FUNCTION: CDate2Julian()

PURPOSE: Convert a date to a Julian day. The function works with dates based on the Gregorian (modern) calendar.

MyDate: A valid Microsoft Access date.

RETURNS: A three digit Julian day as a string.

End function

However, the entire type in the procedure window changes to red font color and it is invalid in the immediate window as well...

Bottom line, can one of you programmers confirm that it is not known how to get the Julian date from a 'Expression' in a field of a query that this new Report will draw data from?

Thanks much, Paul Davis
Dec 8 '06 #1

✓ answered by mattmorris0531

An extremely easy way to do this is
Expand|Select|Wrap|Line Numbers
  1. Format(Date, "y")

14 16727
218 Expert 100+

I worked on this a some time ago, but haven't looked at it in a while
Expand|Select|Wrap|Line Numbers
  1. Function julDay(theDate) As String 
  2. ' Param: date with year 
  3. ' Returns: Julian date version of passed date
  5. Function julDay(theDate) As String 
  6. ' Arg: A date with year 
  7. ' Returns: Julian date version of passed date
  8. Dim monthLen() As Integer 
  9. Dim myMonth As Integer 
  10. Dim i                  As Integer 
  11. Dim numDays As Integer 
  13. If Not IsDate(theDate) Then 
  14.     MsgBox "Invalid Date" 
  15.     julDay = "[ERROR]" 
  16. Else 
  17.     ReDim monthLen(1) 
  18.     monthLen(1) = 31 
  20.     ReDim Preserve monthLen(2) 
  21.     If Year(theDate) Mod 4 = 0 Then 
  22.      monthLen(2) = 29 
  23.     Else 
  24.      monthLen(2) = 28 
  25.     End If 
  27.     ReDim Preserve monthLen(3) 
  28.     monthLen(3) = 31 
  29.     ReDim Preserve monthLen(4) 
  30.     monthLen(4) = 30 
  31.     ReDim Preserve monthLen(5) 
  32.     monthLen(5) = 31 
  33.     ReDim Preserve monthLen(6) 
  34.     monthLen(6) = 30 
  35.     ReDim Preserve monthLen(7) 
  36.     monthLen(7) = 31 
  37.     ReDim Preserve monthLen(8) 
  38.     monthLen(8) = 31 
  39.     ReDim Preserve monthLen(9) 
  40.     monthLen(9) = 30 
  41.     ReDim Preserve monthLen(10) 
  42.     monthLen(10) = 31 
  43.     ReDim Preserve monthLen(11) 
  44.     monthLen(11) = 30 
  45.     myMonth = Month(theDate) 
  46.     For i = 1 To myMonth - 1 
  47.         numDays = numDays + monthLen(i) 
  48.     Next i 
  50.     numDays = numDays + Val(Format$(theDate, "dd")) 
  51.     julDay = Format$(theDate, "yy") & Format$(numDays, "000") 
  52. End If 
  53. End Function 
Credit to Pete Cresswell


Dec 8 '06 #2
218 Expert 100+

A couple of other suggestions:-
Expand|Select|Wrap|Line Numbers
  1. datepart("y", now) + (year(now) - 1900) * 1000 

Expand|Select|Wrap|Line Numbers
  1. juliandate = Right(DatePart("yyyy",[greg_date]),2) & 
  2. Format(DateDiff("d",CDate("01" & "/" & "01" & " /" & 
  3. DatePart("yyyy",[greg_date])),[greg_date])+1,"000") 
No guarantees, tho'


Dec 8 '06 #3
32,534 Expert Mod 16PB
I'm not sure what the Julian version is exactly, but the day in year number can be got by :
Expand|Select|Wrap|Line Numbers
  1. datVar+1-cdate("1/1/" & year(datVar))
where datVar is the date variable.
Dec 8 '06 #4
Hey Gang,

I know that one was a bear. Thank you all for the assistance, though I was unable to make it work. No worries, it was appreciated and I am sure it will help lay the foundational knowledge necessary to working with code in Access.

Have a good one, Paul
Dec 11 '06 #5
8,435 Expert 8TB
I'm not sure what the Julian version is exactly, but the day in year number can be got by :
Expand|Select|Wrap|Line Numbers
  1. datVar+1-cdate("1/1/" & year(datVar))
where datVar is the date variable.
I realise this thread is long finished, but I have been looking into converting a date value to julian format today, and came across it while searching. After trying NeoPa's suggestion (which works, of course) I found that you can get the same value from DatePart - the "y" parameter gives you "Day of year".

So I wrote the following routine in VB6, which for 15 Feb 2007, returns "200746". Should also work in VBA.
Expand|Select|Wrap|Line Numbers
  1. Public Function Julian(ByVal datVal As Date) As String
  2.   Julian = DatePart("yyyy", datVal) & DatePart("y", datVal)
  3.   ' Could also use this...
  4.   ' Julian = Year(datVal) & DatePart("y", datVal)
  5. End Function
I suppose it's a bit of an open question as to which function is the more efficient, DatePart or CDate. Probably not a huge difference, in any case.
Feb 15 '07 #6
32,534 Expert Mod 16PB
It seems to me that DatePart() would be preferable.
It's what I would have chosen if I'd thought about it first ;)
Feb 15 '07 #7
8,435 Expert 8TB
How would one go about converting the other way? That is, Julian to Gregorian? I just realised I have an application which will need to do this shortly.

Obviously I'll find some way to do it, but it's likely to be rather kludgy (like counting through Julian dates until one matches, or something). I'd like to find a nice, clean method.
Feb 15 '07 #8
32,534 Expert Mod 16PB
Use DateAdd("d",Julian bit,#31 Dec Year-1#)
Feb 15 '07 #9
8,435 Expert 8TB
Use DateAdd("d",Julian bit,#31 Dec Year-1#)
I like it! :)

Feb 15 '07 #10
32,534 Expert Mod 16PB
Thank you Killer.
It's always nice to get appreciation from a connoisseur :)
Feb 15 '07 #11
I'm not sure what the Julian version is exactly, but the day in year number can be got by :
Expand|Select|Wrap|Line Numbers
  1. datVar+1-cdate("1/1/" & year(datVar))
where datVar is the date variable.
what you just posted is, in actuality, the Julian Date. And had saved the rest of us, approximately 52 lines of coding otherwise.

Thank you, Neopa. No, seriously...Thanxs

Univ of Utah
Jun 26 '07 #12
32,534 Expert Mod 16PB
Not a problem Shiznaw.
I enjoy seeing shortcuts in code. Sometimes I have to forswear them because they make maintenance too complex, but I feel better if I see them anyway at least ;)
Jun 28 '07 #13
An extremely easy way to do this is
Expand|Select|Wrap|Line Numbers
  1. Format(Date, "y")
May 21 '10 #14
32,534 Expert Mod 16PB
I think you're right Matt. It seems we all overlooked this. I must admit I rarely ever use Julian dates myself.
May 23 '10 #15

Sign in to post your reply or Sign up for a free account.

Similar topics

by: goochey | last post by:
I'm trying to convert a Julian Date (Format "4365") into an actual calendar date in Visual Basic, can anyone help me out with this.
by: Jim Davis | last post by:
Before I reinvent the wheel I thought I'd ask: anybody got a code snippet that will convert the common ISO8601 date formats to a JS date? By "common" I mean at the least ones described in this...
by: Chris Davoli | last post by:
Is there a class that converts from Gregorian date to TRUE Julian Date in ..NET? Any VB.NET examples available? ie; The Julian date is calculated by the number of days since January 1, 4713 BC....
by: Sam | last post by:
How do I convert Julian Date to Calendar Date in ASP.Net 1.1 based on following guideline found at Internet? To convert Julian date to Gregorian date: double JD = 2299160.5; double Z =...
by: mbsevans | last post by:
New Access user here. I have a simple datebase containing employee information. I have 3 differents dates which reflect the DUE date for a qualification that each employee needs in order to continue...
by: heckstein | last post by:
I am running a query in Access 2003 to pull training courses with a start date that falls within a specified date span such 2/1/07 to 2/28/07. I was using this code - (( I.STARTDATE) Between...
by: amanda27 | last post by:
I have a database that we use in our department for the status of our projects. In the form when you pick a project from the dropdown list I have a subform that pulls the data entered for the...
by: saddist | last post by:
Hello, In access 2003 I have date format yyyy/mm/dd, but I want it to be dd/mm/yyyy. I tried to enter dd"/"mm"/"yyyy and dd/mm/yyyy in format property, but it changes automaticly to dd/mm"/yyyy"....
by: chandru8 | last post by:
hi to all iam using vb6.0 can any one correct me the query which below mentioned is correct strSql = "Insert into table1 values (#" & Date & "# ,'2')" Set rs = objCon.Execute(strSql) or
by: Rina0 | last post by:
Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
by: lllomh | last post by:
How does React native implement an English player?
by: Mushico | last post by:
How to calculate date of retirement from date of birth
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.