Julian Date function runs too slow

A

Al

This code works but is very slow. It loops through the Julian Dates in a
table, converts them to Gregorian Dates, and then updates an empty field in
the same table.

The table is a temporary table that is generated through a prior query then
updated using this Julian date function. I am trying to set up a custom
reporting feature from a Pervasive SQL Server ODBC connection where the dates
are stored as Julian dates and then use the converted dates to link back to
dates in another database to gather the data needed for the report.

Below is my working code that converts 13,000 records in about 2 minutes,
which I feel is much too slow. There has to be a better way to do this.

Public Function SampDCal()

'CONVERTS JULIAN DATE TO GREGORIAN CALENDAR DATE (MONTH/DAY/YEAR)
Dim X As Long
Dim Z As Long
Dim F As Long
Dim A As Long
Dim B As Long
Dim C As Long
Dim D As Long
Dim G As Long
Dim UT As Long
Dim m As Long
Dim Y As Long
Dim IDS As Long
Dim Year As Long
Dim Month As Long
Dim Day As Long
Dim rs As DAO.Recordset
Dim RCt As Long
Dim strSql As String
Dim i As Integer
Dim SmpDate As String
Dim db As Database
Dim LUpdate As String

strSql = "Select ID from SampDateLink"

Set db = CurrentDb()
Set rs = DBEngine(0)(0).OpenRecordset(strSql)

RCt = DCount("*", "SampDateLink") 'Count # of records
i = 0
Do While i <= RCt
IDS = rs.AbsolutePosition + 1

On Error Resume Next
X = DLookup("[LocDate]", "SampDateLink", "[ID] =" & IDS)
Z = Int(X)
F = X - Z
Y = Int((Z - 1867216.25) / 36524.25)
A = Z + 1 + Y - Int(Y / 4)
B = A + 1524
C = Int((B - 122.1) / 365.25)
D = Int(365.25 * C)
G = Int((B - D) / 30.6001)

If G < 13.5 Then
Month = G - 1
Else
Month = G - 13
End If

If Month < 2.5 Then
Year = C - 4715
Else
Year = C - 4716
End If

UT = B - D - Int(30.6001 * G) + F
Day = Int(UT)

SmpDate = Month & "/" & Day & "/" & Year

LUpdate = "Update [SampDateLink] set [SampDate] = #" & _
Format(SmpDate, "mm/dd/yyyy") & "# Where [ID] = " & IDS

db.Execute LUpdate

i = i + 1
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set db = Nothing
End Function

Thanks in advance,
Al
 
D

Dirk Goldgar

Al said:
This code works but is very slow. It loops through the Julian Dates in a
table, converts them to Gregorian Dates, and then updates an empty field
in
the same table.

The table is a temporary table that is generated through a prior query
then
updated using this Julian date function. I am trying to set up a custom
reporting feature from a Pervasive SQL Server ODBC connection where the
dates
are stored as Julian dates and then use the converted dates to link back
to
dates in another database to gather the data needed for the report.

Below is my working code that converts 13,000 records in about 2 minutes,
which I feel is much too slow. There has to be a better way to do this.

Public Function SampDCal()

'CONVERTS JULIAN DATE TO GREGORIAN CALENDAR DATE (MONTH/DAY/YEAR)
Dim X As Long
Dim Z As Long
Dim F As Long
Dim A As Long
Dim B As Long
Dim C As Long
Dim D As Long
Dim G As Long
Dim UT As Long
Dim m As Long
Dim Y As Long
Dim IDS As Long
Dim Year As Long
Dim Month As Long
Dim Day As Long
Dim rs As DAO.Recordset
Dim RCt As Long
Dim strSql As String
Dim i As Integer
Dim SmpDate As String
Dim db As Database
Dim LUpdate As String

strSql = "Select ID from SampDateLink"

Set db = CurrentDb()
Set rs = DBEngine(0)(0).OpenRecordset(strSql)

RCt = DCount("*", "SampDateLink") 'Count # of records
i = 0
Do While i <= RCt
IDS = rs.AbsolutePosition + 1

On Error Resume Next
X = DLookup("[LocDate]", "SampDateLink", "[ID] =" & IDS)
Z = Int(X)
F = X - Z
Y = Int((Z - 1867216.25) / 36524.25)
A = Z + 1 + Y - Int(Y / 4)
B = A + 1524
C = Int((B - 122.1) / 365.25)
D = Int(365.25 * C)
G = Int((B - D) / 30.6001)

If G < 13.5 Then
Month = G - 1
Else
Month = G - 13
End If

If Month < 2.5 Then
Year = C - 4715
Else
Year = C - 4716
End If

UT = B - D - Int(30.6001 * G) + F
Day = Int(UT)

SmpDate = Month & "/" & Day & "/" & Year

LUpdate = "Update [SampDateLink] set [SampDate] = #" & _
Format(SmpDate, "mm/dd/yyyy") & "# Where [ID] = " & IDS

db.Execute LUpdate

i = i + 1
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set db = Nothing
End Function
Click to expand...


Let me make sure I understand this. It looks to me like you have a table,
SampDateLink, with (at least) these fields:

ID
- primary key, a sequential number or autonumber
LocDate
- the Julian date you want to convert
SampDate
- the field you want to update with the corresponding Gregorian
date.

If that is right, your code seems to be handling the records in a very
inefficient way. I would recommend separating out the actual
date-conversion logic into a function that receives a Julian date and
returns a standard date/time value, like this:

'------ start of function code ------
Public Function JulianToGregorian(JulDate As Variant) As Variant

'CONVERTS JULIAN DATE TO GREGORIAN CALENDAR DATE (MONTH/DAY/YEAR)

Dim X As Long
Dim Z As Long
Dim F As Long
Dim A As Long
Dim B As Long
Dim C As Long
Dim D As Long
Dim G As Long
Dim UT As Long
Dim m As Long
Dim Y As Long
Dim YearNo As Long
Dim MonthNo As Long
Dim DayNo As Long

If IsNull(JulDate) Then
JulianToGregorian = Null
Exit Function
End If

X = JulDate
Z = Int(X)
F = X - Z
Y = Int((Z - 1867216.25) / 36524.25)
A = Z + 1 + Y - Int(Y / 4)
B = A + 1524
C = Int((B - 122.1) / 365.25)
D = Int(365.25 * C)
G = Int((B - D) / 30.6001)

If G < 13.5 Then
MonthNo = G - 1
Else
MonthNo = G - 13
End If

If MonthNo < 2.5 Then
YearNo = C - 4715
Else
YearNo = C - 4716
End If

UT = B - D - Int(30.6001 * G) + F
DayNo = Int(UT)

JulianToGregorian = DateSerial(Year, Month, Day)

End Function
'------ end of function code ------

Then, with this function defined in a standard module, you simply run an
update query like this:

UPDATE SampDateLink SET SampDate = JulianToGregorian(LocDate)

.... which will update every record in the table.

PLEASE BE AWARE that I have not made any attempt to determine whether your
date-conversion code is correct. There are things about it that bother me;
for example, you define a number of variables as Long (long integers) but
then compare them to floating point values (e.g., "If G < 13.5"). And I
don't know the nature of the Julian date fields you're processing. All I've
really tried to address, except for a few minor tweaks, is the inefficiency
of the record-processing logic.
 
V

vanderghast

Why you don't use a query?

With Jet, (I don't know Pervasive SQL), you can built alias over alias:



SELECT LocDate AS x,
INT(x) AS z,
x-z AS f
INT((z-1867216.25)/ 36524.25) AS y,
...
IIF(g <13.5, g-1, g-13) AS [month]
...
[month] & "/" & [day] & "/" & [year] AS smpDate
FROM SampDateLink




And once you are satisfied with the result, make an update query (if
required), based on that query.


Note that your formulation seems strange: X, F and Z are all dim as LONG,
and so, it appears to me that F will always be zero: z=int(x), but x is
already an integer, so f=z-x = 0. No?
In the SQL statement I proposed, X is left as a date (with eventually, time
portion) because it is not 'dimmed' as long, in the query, so f won't
necessary be always zero, but I am not sure if this is what you want.

Your program construction is not common. I would have used : do while not
rs.EOF, x=rs.locDate, ... , rs.edit, rs.sampDateLink= ..., rs.update,
rs.movenext

without using the absolute position at all. But a query should be even
faster.



Vanderghast, Access MVP


Al said:
This code works but is very slow. It loops through the Julian Dates in a
table, converts them to Gregorian Dates, and then updates an empty field
in
the same table.

The table is a temporary table that is generated through a prior query
then
updated using this Julian date function. I am trying to set up a custom
reporting feature from a Pervasive SQL Server ODBC connection where the
dates
are stored as Julian dates and then use the converted dates to link back
to
dates in another database to gather the data needed for the report.

Below is my working code that converts 13,000 records in about 2 minutes,
which I feel is much too slow. There has to be a better way to do this.

Public Function SampDCal()

'CONVERTS JULIAN DATE TO GREGORIAN CALENDAR DATE (MONTH/DAY/YEAR)
Dim X As Long
Dim Z As Long
Dim F As Long
Dim A As Long
Dim B As Long
Dim C As Long
Dim D As Long
Dim G As Long
Dim UT As Long
Dim m As Long
Dim Y As Long
Dim IDS As Long
Dim Year As Long
Dim Month As Long
Dim Day As Long
Dim rs As DAO.Recordset
Dim RCt As Long
Dim strSql As String
Dim i As Integer
Dim SmpDate As String
Dim db As Database
Dim LUpdate As String

strSql = "Select ID from SampDateLink"

Set db = CurrentDb()
Set rs = DBEngine(0)(0).OpenRecordset(strSql)

RCt = DCount("*", "SampDateLink") 'Count # of records
i = 0
Do While i <= RCt
IDS = rs.AbsolutePosition + 1

On Error Resume Next
X = DLookup("[LocDate]", "SampDateLink", "[ID] =" & IDS)
Z = Int(X)
F = X - Z
Y = Int((Z - 1867216.25) / 36524.25)
A = Z + 1 + Y - Int(Y / 4)
B = A + 1524
C = Int((B - 122.1) / 365.25)
D = Int(365.25 * C)
G = Int((B - D) / 30.6001)

If G < 13.5 Then
Month = G - 1
Else
Month = G - 13
End If

If Month < 2.5 Then
Year = C - 4715
Else
Year = C - 4716
End If

UT = B - D - Int(30.6001 * G) + F
Day = Int(UT)

SmpDate = Month & "/" & Day & "/" & Year

LUpdate = "Update [SampDateLink] set [SampDate] = #" & _
Format(SmpDate, "mm/dd/yyyy") & "# Where [ID] = " & IDS

db.Execute LUpdate

i = i + 1
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set db = Nothing
End Function

Thanks in advance,
Al
Click to expand...
 

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

Similar Threads

Functiosn run too Slow 6
call function module from query - having much trouble 8
Convert Excel vba to Access 3
Need a little help with a custom function 1
Toggle a range of Julian dates to Gregorian Dates and Back 4
Sorting problem : "The sort reference is not valid..........." 3
Extract a YearMonth value from a Date 2
Unknown Jet Error - Date Function 0

Top