Convert Claim # to Julian Dt

[a0a349 via AccessMonster.com]

Hi All!
I have seen lots of posts here on Julian Dates, but it would take me days
looking through everything to potentially find my issue.

I need to convert a claim # to a julian date.
Here is a claim #:
00688043900000, how our julian date would be determined is position 1= year &
positions 2-4 = day or the year.
So, the above claim (0068) SHOULD return a date of 3/8/2010, but instead it
is giving 3/8/2000.
What I am using is working for earlier years but not 2010.

Here is what I am doing.
Step 1: JulianDt: Left(Claim#,4)
Step 2: Rcvdt: DateSerial(Left([JulianDt],1),1,Right([JulianDt],3))

Any thoughts how to get year 2010 to work when I only have one position to
work with?

Thanks!
Jen

 

[XPS350]

Hi All!
I have seen lots of posts here on Julian Dates, but it would take me days
looking through everything to potentially find my issue.

I need to convert a claim # to a julian date.
Here is a claim #:
00688043900000, how our julian date would be determined is position 1= year &
positions 2-4 = day or the year.
So, the above claim (0068) SHOULD return a date of 3/8/2010, but instead it
is giving 3/8/2000.  
What I am using is working for earlier years but not 2010.

Here is what I am doing.
Step 1: JulianDt: Left(Claim#,4)
Step 2: Rcvdt: DateSerial(Left([JulianDt],1),1,Right([JulianDt],3))

Any thoughts how to get year 2010 to work when I only have one position to
work with?

Thanks!
Jen

If 0 always stands for 2010 you could simply add 10 years to the
result, using de DateAdd function.

 

[John Spencer]

And next year you are going to have the problem of 1 being 2001 instead of 2011

You can try something like this which will always assign the year based on the
current decade. So while the current year is 2010 to 2019 the numbers 0 to 9
will generate those years and when the current year rolls over to 2020 then
the numbers will generate 2020 to 2029.

DateSerial((Year(Date())\10) *10 + Val(Left([Claim],1)),1,Mid([Claim],2,3))

Of course if you are interested in just the next decade and want to simplify
you can just add the value of the first character to 2010 to get the desired year.

DateSerial((2010 + Val(Left([Claim],1)),1,Mid([Claim],2,3))

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[J_Goddard via AccessMonster.com]

Hi -

How are you ever going to distinguish 2005 from 2015 using only 1 digit? IMO,
you should, if at all possible, add an additional digit for the year. If
that can't happen, then you are left with assumptions about the single date,
as other responders have pointed out. I hope you keep the converted date too,
since the farther we go into the 2010's, the worse your problem may be, e.g.
sorting all the data (current and old) on the claim number alone simply won't
work.

John

Hi All!
I have seen lots of posts here on Julian Dates, but it would take me days
looking through everything to potentially find my issue.

I need to convert a claim # to a julian date.
Here is a claim #:
00688043900000, how our julian date would be determined is position 1= year &
positions 2-4 = day or the year.
So, the above claim (0068) SHOULD return a date of 3/8/2010, but instead it
is giving 3/8/2000.
What I am using is working for earlier years but not 2010.

Here is what I am doing.
Step 1: JulianDt: Left(Claim#,4)
Step 2: Rcvdt: DateSerial(Left([JulianDt],1),1,Right([JulianDt],3))

Any thoughts how to get year 2010 to work when I only have one position to
work with?

Thanks!
Jen