Using CDate with no value = Type-Mismatch

[option]

Hey guys,

I'm pulling data from an external source, storing processed transactions. The information I obtain has the date formatted as "mmddyy", and is returned as a string. I wrote a function to add the slashes, and to also format another date (which I am using for comparison purposes). This is the function:

' Str_Temp is a 6 digit representation of the date without slashes. Put slashes in
Private Function ConvDt(str_Temp As String) As String
On Error GoTo Err_convt_

   str_Temp = Format(str_Temp, "000000")   'force 6 numbers
   str_Temp = Mid(str_Temp, 1, 2) & "/" & Mid(str_Temp, 3, 2) & "/" & Right(str_Temp, 2) ' add slashes

   ConvDt = str_Temp


Exit_convt_:
    Exit Function

Err_convt_:
    MsgBox Err.Description
    Resume Exit_Command0_Click
End Function

And to call this, I'm using CDate here:

If CDate(ConvDt(Trim(RefApp.gettext(L, 0, L, 6)))) < CDate(HrDte) Then

                    rs.MoveNext
                    GoTo chgRec
                End If

It works great...until I get to the end of the external records and hit a blank value. This function, when run with no date, returns "//" and because it's not a date value, I get "type mismatch / err. # 13". I need to figure out how to work around this and continue through my table of records so I can get these transactions statused. Any ideas??

 

[ByteMyzer]

Try the following:

[b]If IsDate(ConvDt(Trim(RefApp.gettext(L, 0, L, 6)))) = True Then[/b]
    If CDate(ConvDt(Trim(RefApp.gettext(L, 0, L, 6)))) < CDate(HrDte) Then
        rs.MoveNext
        GoTo chgRec
    End If
[b]Else
    rs.MoveNext
    GoTo chgRec
End If[/b]

 

[option]

That's just going to do what I've already done with the Mids in there.

I tried it though, because any advice is better than none, and it did exactly the same thing as it did the way I have it coded.

 

[gemma-the-husky]

add this first

if nz(str_temp,0)=0 then
..handle this exception of an invalid date in some way first
end if

'if its a string it needs to be
if nz(str_temp,vbullstring)=vbnullstring then
..handle this exception of an invalid date in some way first
end if

 

[option]

Gemma, you're a genius!

' Str_Temp is a 6 didget representation of the date without slashes. Put slashes in
Private Function ConvDt(str_Temp As String) As String
On Error GoTo Err_Command0_Click

 If Nz(str_Temp, vbNullString) = vbNullString Then
    str_Temp = "000000"
 Else
   str_Temp = Format(str_Temp, "000000")   'force 6 numbers
   str_Temp = Mid(str_Temp, 1, 2) & "/" & Mid(str_Temp, 3, 2) & "/" & Right(str_Temp, 2) ' add slashes
 End If
   
   ConvDt = str_Temp


Exit_Command0_Click:
    Exit Function

Err_Command0_Click:
    MsgBox Err.Description
    Resume Exit_Command0_Click
   End Function

That works perfect!! Thank You!!!