Null CDate issue

[linusx]

Hello Everybody,

I have this query:

SELECT Affiliate_id, Date_Payment_Received FROM Class_Students
WHERE IIF(ISNULL(CDate([Date_Payment_Received])), CDate('1/1/1980'), CDate([Date_Payment_Received])) > #1/1/2006#

It always returns "Invalid Use Of Null"

Date_Payment_Received is a text field, not a date field, and may or may not contain a valid date.

Any help would be great.

Thanks,
Bill

 

[PHV]

SELECT Affiliate_id, Date_Payment_Received
FROM Class_Students
WHERE CDate(Nz([Date_Payment_Received],'1980-01-01'))>#2006-01-01#

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[linusx]

Hello PHV,

Thanks, but I just realized that some of the rows contain text strings and are not null. Those rows return #ERROR and still can't sort by those dates. Any ideas?

Thanks,
Bill

 

[dhookom]

Convert the field to a date data type after updating the non-date values.

Duane
Hook'D on Access
MS Access MVP

 

[linusx]

Duane,

Thanks, not sure what you mean. Could you give me an example? SQL queries are not my strong point specially Access.

Thanks,
Bill

 

[dhookom]

Apparently you have a text field named "Date_Payment_Received" that might store a legitimate date or might not. I would make this into a date field. You may need to do some editing of the values that can't be resolved easily into date values.

Duane
Hook'D on Access
MS Access MVP

 

[linusx]

Duane,

Yes that is what I have, but currently it is not possible to change this field. I would love to, but it is an existing application that runs a lot of other pieces.

Thanks,
Bill

 

[dhookom]

You could create a user-defined function that returns either the date or Null or an alternative date. Add this function to a new, blank module and save the module as "modDateFunctions".

'---------------------------------------------------------------------------------------
' Procedure : MakeADate
' Author    : hookomd
' Date      : 3/9/2009
' Purpose   : replace a text date field with a variant date or null
'             varDate is the text date field which might be null
'             booAllowNull is yes for allowing return of NULL else no
'             datAlterDate is the replacement date if the varDate can't be resolved
'---------------------------------------------------------------------------------------
'
Public Function MakeADate(varDate As Variant, _
        Optional booAllowNull As Boolean = True, _
        Optional datAlterDate As Date) As Variant
    Dim varReturnValue As Variant
    On Error GoTo MakeADate_Error

    If IsNull(varDate) And booAllowNull = True Then
        varReturnValue = Null
     Else
        If IsDate(varDate) Then
            varReturnValue = CDate(varDate)
         Else
            varReturnValue = datAlterDate
        End If
    End If
    MakeADate = varReturnValue

    On Error GoTo 0
    Exit Function

MakeADate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure MakeADate of Module basDateFunctions"
End Function

You can then use this function in your query like:

SELECT Affiliate_id, Date_Payment_Received
FROM Class_Students 
WHERE MakeADate([Date_Payment_Received],False, #1980-01-01#)>#2006-01-01#;

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Another way is to create a query retrieving the rows with a valid date, say, qryDatePayment:

SELECT Affiliate_id, Date_Payment_Received
FROM Class_Students 
WHERE IsDate([Date_Payment_Received])=True

And now your query:

SELECT Affiliate_id, Date_Payment_Received
FROM qryDatePayment 
WHERE Date_Payment_Received>#2006-01-01#

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[linusx]

Duane,

Thanks a lot the module worked out great.

Thanks so much.

Bill

 

[linusx]

Haha, after all this I just found out using the module outside of Access, I.E. using ASP; it can not be done with Access. So I am testing it with the other query.

Thanks,
Bill

 

[dhookom]

I must have missed your earlier posting stating this was for an ASP page ;-).

Duane
Hook'D on Access
MS Access MVP