Query or VBA

[Vikki]

Hi



I need help converting a production code into a date. The production code consitis of 4 numbers. Not sure if I can use a query or need to use VBA.

Eg, 0029 = 29/01/2010

The first number represents the year then followed by 3 numbers that represent the day number

9001 = 01/01/2009
9365 = 31/12/2009
0001 = 01/01/2010
0365 = 31/12/2010

Any help would be greatful

Thanks

[dcrake]

There is not a long life span on this methodology, however, you will be best using a public function to convert this to a date.

 
Public Function CodeToDate(AnyCode As String) As Date
 
'First evaluate the first digit
 
Dim Y as String
Dim D as Date
 
'Add the century and the 2 digit year
Y = "20" &  Format(Left(AnyCode,1),"00")
 
'PAss the first day of the given year to a variable
D = CDate("01/01/" & Y)
 
'Add the number of days to this based on the code
D = DateAdd("d",Val(Mid(AnyCode,2)),D)
 
CodeToDate = D
 
End Function

Save this in a standard module then in your query call the function

Alias:CodeToDate([ProductCode])

David

[Vikki]

Cheers Dcrake

It has worked a treat! I was just about to resort to head banging the desk!

Thanks again.

[RuralGuy]

It sounds like you are ready to follow the link in my sig and mark this thread as Solved.

[dcrake]

If you 3 digit part of the code is one day out then

at the bottom of the code change the following

CodeToDate = DateAdd("d",-1,D)


David

[Vikki]

If you 3 digit part of the code is one day out then

at the bottom of the code change the following

CodeToDate = DateAdd("d",-1,D)


David

Thanks for that! manged to work it out, but great to see what I did is how someone else would have done it.

Thanks