Need help with query and new year

[crashfo0]

Hey all. I'm not an expert with Access 2003, but am learning more everyday. With my job, we track a lot of stuff using Microsoft Access 2003 and have had some issues since January 1, 2010. Unfortunately, the people that created our database are long gone and I've had to try to figure things out myself. Here is my issue:

We use Julian dates to keep track of flights. Right now we have a query that uses these expressions:

ExprDT: CJulianToDate([Depart] Mod 1000,2000+(Int([Depart]/1000)))
ExprRT: CJulianToDate([Return] Mod 1000,2000+(Int([Return]/1000)))
ExprAL: CJulianToDate([ArrivalDate] Mod 1000,2000+(Int([ArrivalDate]/1000)))
ExprDL: CJulianToDate([DepartDate] Mod 1000,2000+(Int[DepartDate]/1000)))

Then another field in that query that tracks our current flights still out:

Field: Return Home
Table: tblFlights
Criteria: >CDatetoJulian(Date())

Right now the query shows nothing, even though we have flights back from 2007 in our database along with current flights. The format we use for julian dates are 7001, 8001, 9001, etc. So January 1, 2010 should be 0001.

I have noticed at some points that 0001 converts to January 1, 2000. Is there a way to make Access automatically make 0001 January 1, 2010?

Hopefully I didn't get too confusing during this, if you need more info, please let me know.

 

[Guus2005]

Yes, it is very confusing.
You use a function to calculate the Julian Date from the Gregorian Calendar?

Can you post that function: CDatetoJulian and CJulianToDate?

ExprDL: CJulianToDate([DepartDate] Mod 1000,2000+(Int[DepartDate]/1000)))

The second parameter of that function: "2000+(Int[DepartDate]/1000)" might be the problem but i can't say for sure.

DepartDate is Julian format?

 

[crashfo0]

I'd love to post that function if i knew where to find it haha. I just assumed it was something built into Access. I know the people that originally created this used a lot of SQL with it so I'm assuming there is somewhere with a lot of code that might have that function? I'll look around.

 

[Brianwarnock]

I don't know much about Julian dates and have never worked with them but I strongly suspect that using a single digit Y is not a good idea yyddd may not have given any trouble.

Brian

 

[crashfo0]

I agree, but if I did that, I would have to go through and changed thousands of records along with changing over 50 different tables.

 

[crashfo0]

Also, we use all the data and output it to kml files for Google Earth. This is what I just noticed. I changed the criteria from ">CDatetoJulian(Date())" to just say "<7000" which would basically make all the julian dates 0001, 0002, 0003, etc show up. When I load that into Google Earth, the time slide starts at January 1, 2000. So somewhere, julian dates starting with zero are converting to the year 2000 instead of 2010.

 

[crashfo0]

Alright I found the information you were looking for in the modules. Here is whats in there:

Function CJulianToDate(JulDay As Integer, Optional YYYY)
If IsMissing(YYYY) Then YYYY = Year(Date)
If Not IsNumeric(YYYY) Or YYYY \ 1 <> YYYY Or YYYY < 100 Or YYYY > 9999 Then Exit Function
If JulDay > 0 And JulDay < 366 Or JulDay = 366 And YYYY Mod 4 = 0 And YYYY Mod 100 <> 0 Or YYYY Mod 400 = 0 Then CJulianToDate = Format(DateSerial(YYYY, 1, JulDay), "m/d/yyyy") End Function _______________________________________________________________________________________________________________

Function CDateToJulian(MyDate As Date) As String
CDateToJulian = ((Year(MyDate) Mod 1000) * 1000) + Format(DateSerial(Year(MyDate), Month(MyDate), Day(MyDate)) - DateSerial(Year(MyDate) - 1, 12, 31), "000")
' CDateToJulian = CDateToJulian + (MyDate Mod 1000)
End Function

 

[Brianwarnock]

So somewhere, julian dates starting with zero are converting to the year 2000 instead of 2010.

Which is correct and why you need a 2 digit year, its shades of the millenium all over again when short dates didn't have a century.

Brian

 

[Brianwarnock]

One or two other things

Dateserial will sort out leap years for you and also return the date in the system format, plus a year of 0 is 2000 as far as dateserial is concerned.

brian

 

[crashfo0]

Roger that. Cool. I had to go in and make sure our julian dates went to 5 digits instead of 4 like we used to use. Took a little while, but the query is working now. Thank you all for the help. We will just need to change how we input our julian dates, but i'd rather make that minor change than stress over this anymore.

 

[raskew]

Hi -

Think you are creating an unnecessary monster.

Personally, I have never understood the desire to use the 'julian date', particularly with all the date-related functions available in Access.

I'm curious as to your definition of 'julian date'. If you did a Google on the term, you'd find it represented as a
7,980-year cycle that began on January 1, 4713 BC'. However, most times when a reference is made to
it nowadays, e.g. military usage, it refers to some version of the day of the year.

If that's the case, the following will return the day of the current year (today is 28 Jan 2010):

From the debug (immediate) window
? datepart("y", date())
28

Here's what your CDatetoJulian does for you:
? CDateToJulian(date())
10028
...while
? CDateToJulian(#1/28/2009#)
9028

Suggest you consider returning to common ordinary dates. They're understood by all.

Bob