convert yyyyddd to yyyymmdd from and linked table

[LTDragon07]

I'm querying a linked table and trying to reformat the date to yyyymmdd from yyyyddd
example data
DATA OUTPUT
2015001 2015/01/01
2015125 2015/04/05
what I have now in he query
OUTPUT: Left([DATA],4) & "/" & Right([DATA],3)
2015125 2015/125
which only gets me to so far I don't know how to convert the ddd to mmdd

 

[james_lankford]

Option Explicit


Sub doit()


    Dim s As String
    s = "2015125"
    
    Dim d As Date
    
    d = DateAdd("y", CLng(Right(s, 3)) - 1, DateSerial(Left(s, 4), 1, 1))
    
    MsgBox d


End Sub

 

[elmer007]

Edit: deleted post- inaccurate...

 

[ranman256]

You cant do that, its not a true date, so:
put the code in a module, and
in query call: ConvertStr2Date([DATA])

'--------
Function ConvertStr2Date(ByVal pvText)
'--------
Dim vDate
Dim iDoy As Integer, iYr As Integer


iYr = Left(pvText, 4)
iDoy = Right(pvText, 3)


vDate = CJulian2Date(iDoy, iYr)
ConvertStr2Date = Format(vDate, "yyyymmdd")
End Function


'--------
Function CJulian2Date(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 _
             CJulian2Date = DateSerial(YYYY, 1, JulDay)
End Function

 

[RoryA]

You could also simplify to:

d = DateSerial(Left(s, 4), 1, 1) + Right(s, 3) - 1

 

[LTDragon07]

Thanks this code works amazing, exactly what I was looking for.
Never knew how that DateSerial function worked.

You could also simplify to:

d = DateSerial(Left(s, 4), 1, 1) + Right(s, 3) - 1