JULIAN DATE TO CAL DATE PROCEDURE 1

[Raptor136]

Howdy: My employer puts a julian date on all reports. I need to be able to convert the julian to normal date. In order to get an accurate day count between the Julians. For example 2102 = the 102nd day of 2002, 9102 = the 102nd day of 1999.

Here's what I came up with, does anyone have a less cluncky way?

UPDATE DISTINCTROW root000 SET root000.date_gd = IIf([root000]![date]>'4000',DateSerial(Val('199' & Mid([root000]![date],1,1)),Month(Val(Mid([root000]![date],2,3))+1),Day(Val(Mid([root000]![date],2,3))+1)),DateSerial(Val('200' & Mid([root000]![date],1,1)),Month(Val(Mid([root000]![dcd],2,3))+1),Day(Val(Mid([root000]![date],2,3))+1)))
WHERE ((([root000]![date]) Not In (' ')));

The problem with this is that it works fine in the SQL view of the Query box. But if I paste it into the VBA and try to make an event out of it, it just keeps piling up different errors.

TIA: -- Rap

 

[Steve101]

Rap,

(a) Set up a public function in a module to do the conversion. Sample code follows below:

   Function ConvertJulianDate(JDate As String) As Date
   '---------------------------------------------------
   'This function converts a Julian date in the form of
   'yddd to a conventional date. The "pivot" year is
   'assumed to be 5.
   '---------------------------------------------------
   On Error GoTo ConvertJulianDateError

   Dim y As Integer, yyyy As Integer
   Dim ddd As Integer

   y = Left(JDate, 1)
   If y > 4 Then
      yyyy = 1990 + y
   Else
      yyyy = 2000 + y
   End If
   ddd = Mid(JDate, 2)
   ConvertJulianDate = DateSerial(yyyy, 1, ddd)

   ConvertJulianDateExit:
   Exit Function

   ConvertJulianDateError:
   ConvertJulianDate = Null
   Resume ConvertJulianDateExit

   End Function

You can then use this function anywhere in your application to do the application.

(b) An example of usage of this function would be in an action query in your query window; for example:

   UPDATE root000 
   SET    date_gd = ConvertJulianDate([Date])

(c) You cannot simply transfer SQL from the Query window to a code procedure (ie. function of sub). SQL Syntax is not recognised directly in code, so you do need to tell the code what do do with it, by wrapping the SQL up as a string (or querydef), then applying a recognised code based command to "operate" on the SQL String/Querydef object. For example, with the above SQL, to run it from code:

   Docmd.runsql "UPDATE root000 " & _
                "SET date_gd = ConvertJulianDate([Date]) "

Good luck Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Raptor136]

Awesome Steve -- Thanks again. Ever thought about teaching? -- Rap

 

[Steve101]

Thanks Rap,
Yes done a little part time teaching, and do enjoy it. Keep at it,
Regards, Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)