Pulling a date from a two digit year and julian date

[ynott]

I have a field with an event number. Example, 06-001-019. 06 is for the year, 001 is the julian date, and 019 for the event of the day. My goal is to create a query that will display the date of the event automatically. Any ideas? TIA

 

[PHV]

DateOfEvent: DateSerial(Left([EventNumber],2),0,Mid([EventNumber],4,3))

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

 

[AlexCuse]

shouldn't a julian date look more like this:

245 0448.5

(That is for start of January, 1997). So 001 would be from some time in the year 4713 B.C. I don't believe this date can be represented by access, but you may be able to put together a formula to create the date as a string and display that.

Have a look here, maybe it will help you put together the formula you need. You will probably need to use a custom VBA function rather than writing a convoluted query.

Good Luck,

Alex



Ignorance of certain subjects is a great part of wisdom

 

[ynott]

001 represents January 1st in the event number if that helps clear it up.

 

[AlexCuse]

Ok I get it. I am not too familiar with PH's use of DateSerial, but if he suggests it it probably will work.

If it doesn't, here is an idea for a function (typed here, not tested)

public function GetEventDate(EventNumber as String)

dim MyDate as String

MyDate = "#20" & left(EventNumber, 2) & "0101#"

GetEventDate = DateAdd("dd", Mid(EventNumber, 4, 3) - 1, MyDate)

end function

Called from a query like this:

EventDate: GetEventDate(EventNumber)

Hope it helps,

Alex


Ignorance of certain subjects is a great part of wisdom

 

[ynott]

I think this is the answer for those that are interested. In the future, maybe I'll notice an error, but so far it's working.

CreateDate: DateSerial((2000+CLng(Left([EventNumber],2))),1,CLng(Mid([EventNumber],4,3)))