Function to make a date Null when input string is Null 1

[madelca100]

I have the following situation:
Access 97 linked via ODBC to Oracle db (not sure of version - don't think it matters).

Dates are stored as character in format "yyyymmdd" (not my design).

I wrote my first Access function as follows

Function convertYYYYMMDD2Date(strDateField As String) As Date

If IsNull(strDateField) Or Not IsNumeric(strDateField) Then
Exit Function
Else
convertYYYYMMDD2Date = DateValue(Mid(strDateField, 5, 2) & "/" & Mid(strDateField, 7, 2) & "/" & Mid(strDateField, 1, 4))
End If
End Function

This does convert dates properly
(e.g., 20010625 converts to 6/25/2001)

However it converts nulls to #Error.

I was hoping to trap the nulls with the first line of code. (Based on searches to this forum.)

I've also tried assigning Null (convertYYYYMMDD2Date = Null) with the same effect.

How do I make the date null when the input is also null?

thanks,
mike

 

[PHV]

Have you tried this ?
Function convertYYYYMMDD2Date(strDateField As String) As Variant
If IsNull(strDateField) Or Not IsNumeric(strDateField) Then
convertYYYYMMDD2Date = Null
Else
convertYYYYMMDD2Date = DateValue(Mid(strDateField, 5, 2) & "/" & Mid(strDateField, 7, 2) & "/" & Mid(strDateField, 1, 4))
End If
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[madelca100]

Sorry PHV - same result (except left justified instead of right justified)

mike

 

[Zion7]

According to your first example, medalca100, it appears, your problem is with the initial comparison; If IsNull(strDateField) or Not iSNumeric(strDateField), because Exit function should remove any doubt of the results.

you are using a string variable to extract data from the datefield. Since it's a string, if it recieves a null value, strDate will equal "", not null or numeric.

So your comparison must be..

If strDateField = "" Then
Exit function

but to be on the safe side, you may want to include all 3, in case you're not sure whether all invalid entries are exclusively, null.


If IsNull(strDateField) or Not IsNumeric(strDateField) Or strDateField = "" Then...

Hope this Helps, good luck!

 

[madelca100]

dboulas - Although it's not in the code, I'm sure I tried testing for empty string (""). I will double check on Tuesday when I'm back in the office.

I even tried assigning a date of 1/1/01 with no luck.

Any other suggestions would be appreciated.

mike

 

[Zion7]

Fair enough madelca100, I guess my point was, the problem appears to be in the If Statement, maybe that was obvious at this point. I'm not sure if your "Empty" values are returning "Null". Maybe declare your variable as a variant, & use a few different , "Null", "" 0 values. as criteria.

but like you said, you've probably done extensive checking at this point.

Good luck either way!

 

[RoyVidar]

Try pass a variant in stead of string to the function. Would also apply to the datatype of the return value, if you need it to return Null.

[tt]Function convertYYYYMMDD2Date(strDateField As Variant) As Variant
If IsNull(strDateField) Or Not IsNumeric(strDateField) Then
convertYYYYMMDD2Date=Null
Else
convertYYYYMMDD2Date = DateValue(Mid(strDateField, 5, 2) & _
"/" & Mid(strDateField, 7, 2) & "/" & Mid(strDateField, 1, 4))
End If
End Function[/tt]

If there's a possibility the field could be "", contain only space(s), you might perhaps take a look at something like this in stead of the IsNull test:

[tt]if len(trim$(strDateField & vbnullstring))=0 then
' is null, "" or contains only spaces[/tt]

Roy-Vidar

 

[Zion7]

Yes Roy, Very good point, BOTH values should be variants, an oversight on my part. And the extra clause in the if statement, wouldn't hurt.

 

[madelca100]

Thanks RoyVidar - Variant did the trick.

I just scanned the manual on Variant and have some more experimenting to do. Thanks for the tip.

mike