Problem extracting date from Julian date

[Mainer]

Can someone please tell me what is wrong with my code below. I am attempting to extract a date of birth from a text file. I am able to convert the day and month portion of the date as seen at strDate3 but when I try to get the year, I get a "Run-Time Error 13, Type Mismatch"
In the text file the date of birth is stored as yyyyddd which is 1966031 for (01/31/66). I account for leap years by using the code &quot;If ((strYear3 Mod 4 = 0) And (strYear3 Mod 100 <> 0) Or (strYear3 Mod 400 = 0)) Then .....

It is the if statement that generates the error message. When I comment out the &quot;strYears3 = Mid(PULL_record, 229, 4), the code runs and returns the correct day and month

Dim strYear3
Dim strDate3
Dim Date_of_Birth As String
Dim strJulianDate


'strYear3 = Mid(PULL_record, 229, 4)
strDate3 = Mid(PULL_record, 233, 3)
strJulianDate = strYear3 & strDate3

If ((strYear3 Mod 4 = 0) And (strYear3 Mod 100 <> 0) _
Or (strYear3 Mod 400 = 0)) Then
.............

 

[johnwm]

You can't do Mod arithmetic on a string. Just do a Cint on it first:
strYear3 = Cint(Mid(PULL_record, 229, 4))

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[Mainer]

Thanks for the advice. I did try CInt as you suggested but now I get an error on that very line of code. I've tried other data types of numbers and still no luck.

 

[johnwm]

Stick a Debug in:

Debug.Print &quot;*&quot; & (Mid(PULL_record, 229, 4)) & &quot;*&quot;

just before the erroring line to see exactly what you get. If the problem isn't obvious from that, post that result here for us to see. Also show the error message you're getting

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[Mainer]

At the risk of being a complete noobie, when I put the debugger in as you suggest, the only thing that happens is that it fails on:

strYear3 = Cint(Mid(PULL_record, 229, 4))

The error message I get is &quot;Run-time error '13', type mismatch&quot;

Is there a way to post screen shots or attachments at this site? I have a screen shot that shows the Date_of_Birth variable having the correct date in it when I hover the mouse over it when the program fails.

 

[johnwm]

If you have put the debug line before the erroring line, then look at the Immediate window (Ctrl-G) and you'll see the debug info.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[Mainer]

I put the debugger before:
strYear3 = CDbl(Mid(PULL_record, 229, 4))

It now fails on that line of code.

The immediate window shows * *

Curiously, when I hover the mouse over strYear3 anywhere, it has the correct year. It is failing on that line of code but has the correct data in it.

 

[johnwm]

If the debug window shows * * then you will crash on Cint or Cdbl as space characters won't convert to numbers

Is your code looping through the file? If it is, then strYear3 may be carrying data from the last loop and you may have run past the end of the data.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[jbradley]

Try this. It takes a date in the format yyjjj and returns a date in the format mm/dd/yy.

Call it with strDate=JulianToGregorian(yyjjj)

Public Function JulianToGregorian(Julian As String) As String
Dim dtStartDate As Date
On Error GoTo ErrorHandler
    If Trim(Julian) = "" Then
        JulianToGregorian = ""
        Exit Function
    End If
    dtStartDate = "01/01/" & Left(Julian, 2)
    JulianToGregorian = Format(DateAdd("d", Right(Julian, 3) - 1, dtStartDate), "mm/dd/yy")
    Exit Function
ErrorHandler:
    If Err.Number = 13 Then Resume Next
End Function