Replace 12/31/1899 with Null (1 Viewer)

[projecttoday]

I have a function that I use in queries to convert numbers into dates. If a number is not a value we are looking for, then the function should return a null. The dates come out correct. The numbers that do not qualifty come out as 12/31/1899.

This is the function:

Public Function ConvertDate(InDate As Long) As Date
Dim lngIndate As Long, strIndate As String
'  if it is not a valid date in the yyymmdd format, set it to
If InDate < 1010101 Or InDate > 1300101 Or InDate = 999999 Then
    [B]ConvertDate = vbNull      <--- this is where 12/31/1899 comes in[/B]
Else
    lngIndate = InDate + 19000000
    strIndate = lngIndate
    ConvertDate = Mid(strIndate, 5, 2) & "/" & Mid(strIndate, 7, 2) & "/" & Left(strIndate, 4)
End If
End Function

Invalid dates in the input come up as 12/31/1899 in Access. Since I don't want 12/31/1899 to show on my forms and reports I have to add code that sets 12/31/1899 to "". It would be simpler if i could put an actual null value in the table. Is this possible? Changing vbNull to Null or "" causes an error.

 

[KenHigg]

Have you tried setting it to zero - '0'?

 

[boblarson]

Since your function returns a DATE value:

Public Function ConvertDate(InDate As Long) As Date

it can't be assigned a null at that point. You can do it where you are USING the function though.

 

[ChrisO]

The Date data type can not be assigned a Null but the Variant can.

Public Function ConvertDate(InDate As Long) As Date

Public Function ConvertDate(InDate As Long) As Variant

Edit to add
And I forgot to mention:-

Use…
ConvertDate = Null
not
ConvertDate = vbNull

vbNull is not Null, it has a value of 1.


Chris.

 

[mdlueck]

Invalid dates in the input come up as 12/31/1899 in Access. Since I don't want 12/31/1899 to show on my forms and reports I have to add code that sets 12/31/1899 to "".

Sounds similar to this code I wrote to convert form YYYDDD Ordinal Date Format dates: http://en.wikipedia.org/wiki/ISO_8601#Ordinal_dates

Public Function dateutils_ConvertFromYYYDDD(strYYYDDD As String) As String
  On Error GoTo Err_dateutils_ConvertFromYYYDDD

  Dim intDay As Integer
  Dim intYear As Integer

[COLOR=Blue][B]  'Test for "blank" dates
  If strYYYDDD = "0" Then
    dateutils_ConvertFromYYYDDD = vbNullString
    Exit Function
  End If
[/B][/COLOR]
  'Store the day of the year
  intDay = Right(strYYYDDD, 3)

  'Determine if year is 2 digits or 3 digits on the inbound date — ie. 99001 or 104001
  If Len(strYYYDDD) = 5 Then
    intYear = Left(strYYYDDD, 2) + 1900
  Else
    intYear = Left(strYYYDDD, 3) + 1900
  End If

  dateutils_ConvertFromYYYDDD = DateSerial(intYear, 1, intDay)

Exit_dateutils_ConvertFromYYYDDD:
  Exit Function

Err_dateutils_ConvertFromYYYDDD:
  Call errorhandler_Logger("Module: modshared_dateutils, Function: dateutils_ConvertFromYYYDDD()")
  dateutils_ConvertFromYYYDDD = vbNullString
  Resume Exit_dateutils_ConvertFromYYYDDD

End Function

Before I added the blank check, I would also end up with a funky date back in the 1800's. So I chose to take care of that sharp spot once in the shared conversion function.

 

[projecttoday]

Thanks all for the posts.

I had the function defined as string at first. I had this idea that it would be better to return a date. I should have left it as string. As ChrisO suggested, I changed Date to Variant and vbNull to Null. That seems to be working and maybe I'll just leave it at that. Thanks.

 

[gemma-the-husky]

dates are a problem to be honest

eg - one issue is when you need to include a date in a unique key. if you leave the date blank you can actually get duplicate values in a unique key (try it and see!). so you may have to use zero instead of a blank, to allow for a unique key, and then explicitly test for the value zero as a special case/

 

[projecttoday]

I don't particularly care for not having the option of date only or time only.