Module Help

[Robert_DubYa]

I have a module I created and when used in a query it runs fine as long as I
do not set criteria against that field. The moment I set criteria against
the the field I get a "Type Mismatch error". The table is huge. 1M records.
I need to find where the mismatch is in order to get this query work
correctly.

I created a simple module to convert a date/time stored as a number from
another db to be used as normal date time data type. The number I'm
converting looks like:

200809260730

The first four numbers are the year, 5th and 6th the month, the 8th and 9th
the day, 10th and 11th the hour and the last two are the minutes. The
converted date/time is:

9/26/2008 7:30am

My module to convert is simple:

Option Compare Database

Option Explicit

Function ConvertKronos(KronosDate As Double)

On Error GoTo Err_Kronos

If KronosDate = 0 Then
ConvertKronos = 0
Else
ConvertKronos = CDate(Mid(KronosDate, 5, 2) & "/" &
Mid(KronosDate, 7, 2) & "/" & Left(KronosDate, 4) _ '****this line is
overflow and is part of the above
& " " & Mid(KronosDate, 9, 2) & ":" & Mid(KronosDate, 11, 2) &
":" & Mid(KronosDate, 13, 2)) '****this line is overflow and is part of the
above
End If

Exit_Kronos:
Exit Function

Err_Kronos:

MsgBox Err.Description
Resume Exit_Kronos

End Function

If you know why my error is occuring or if you could help in anyway I would
be greatful.

thanks,
Robert

 

[Douglas J. Steele]

Your number is only 12 characters long, but you're trying to refer to its
13th and 14th characters.

Incidentally, your function could be simplified to:

Function ConvertKronos(KronosDate As Double)

On Error GoTo Err_Kronos

If KronosDate = 0 Then
ConvertKronos = 0
Else
ConvertKronos = CDate(Format(KronosDate, "####\-##\-## ##\:##"))
End If

Exit_Kronos:
Exit Function

Err_Kronos:
MsgBox Err.Description
Resume Exit_Kronos

End Function

 

[JvC]

You are trying to do string manipulation on a double. Won't work. You
either need to pass a string to the function:
Function ConvertKronos(KronosDate As String) as date

or convert KronosDate to a string when it gets there.

Robert_DubYa explained on 9/26/2008 :

 

[Robert_DubYa]

Doug,

Thank you for your reply. I had an error in my first post, I actually have
seconds on this as well. (the 13th and 14th characters).

I have paisted and saved your solution in my DB but I get the same error. I
think the data is missing or in an unexpected format from time to time. Is
there a way to query for the error? This would probably help me correct my
code (well your code now).

 

[JvC]

This is my second attempt at a reply, so I'm sorry if it's a dupe.

You are trying to do string manipulation on a double. You either need
to pass a string to the function, or convert KronosDate to a string in
the function.

Robert_DubYa explained on 9/26/2008 :

 

[Douglas J. Steele]

Function ConvertKronos(KronosDate As Double)
On Error GoTo Err_Kronos

If IsDate(Format(KronosDate, "####\-##\-## ##\:##")) Then
ConvertKronos = CDate(Format(KronosDate, "####\-##\-## ##\:##"))
Else
ConvertKronos = 0
End If

Exit_Kronos:
Exit Function

Err_Kronos:
MsgBox Err.Description
Resume Exit_Kronos

End Function