Data Type error in WHERE, but not in SELECT

[foshdafosh]

Hello there all,

I'm having a bit of a problem with a SQL query in MS Access.
I need to produce a list of all anniversaries of a date (ddate) that
fall between two dates.

I've created this function in VBA:
-------------------------------------------------------
Function NextDateOcc(strIn As String) As Date
On Error GoTo DateError
' Dim strSplitChar As String, varDateParts As Variant

If IsNull(strIn) = True Or Len(strIn) = 0 Then
NextDateOcc = "01/01/1900"
Exit Function
End If

' varDateParts = Split(strIn, strCharSpace)

' strSplitChar = "/"

' varDateParts = Split(varDateParts(0), strSplitChar)

' NextDateOcc = DateValue(varDateParts(0) & "/" & varDateParts(1) &
"/" & Year(Now()))
' If NextDateOcc < Now() Then NextDateOcc = DateValue(varDateParts
(0) & "/" & varDateParts(1) & "/" & (Year(Now()) + 1))

' NextDateOcc = Format(varDateParts(0) & "/" & varDateParts(1) &
"/" & Year(Now()), "dd/mm/yyyy")
' If NextDateOcc < Now() Then NextDateOcc = Format(varDateParts(0)
& "/" & varDateParts(1) & "/" & (Year(Now()) + 1), "dd/mm/yyyy")


NextDateOcc = Format(Format(strIn, "dd/mm") & "/" & Format(Now(),
"yyyy"), "dd/mm/yyyy")
If NextDateOcc < Now() Then NextDateOcc = Format(Format(strIn, "dd/
mm") & "/" & (Format(Now(), "yyyy") + 1), "dd/mm/yyyy")

If IsDate(NextDateOcc) = False Then NextDateOcc = "01/01/1900"

Exit Function

DateError:
MsgBox Err.Description
NextDateOcc = "01/01/1900"
Exit Function
End Function
-------------------------------------------------------

The commented out bits are me clutching at straws trying to find the
problem.


When I use this function in the SELECT bit of an SQL query, it works
fine. I can bring back every single record in a table and each row is
correct.
However the below query gives me a data type error when I try to run
it:
-------------------------------------------------------
SELECT *
FROM My_Test_Table
WHERE NextDateOcc([ddate])>=#1 Aug 2009#
AND NextDateOcc([ddate])<=#7 Aug 2009#;
-------------------------------------------------------

Any help much appreciated!

Thanks,
Pete

 

[vanderghast]

In VBA, only a Variant can hold a database-null, a Sting cannot hold a null,
so if your ddate can be null, change your function signature to:

Function NextDateOcc(strIn As Variant) As Date

and return a date in NextDateOcc if it is true that IsNull(strIn).



Vanderghast, Access MVP

 

[foshdafosh]

Thank you so very much.
That's sorted it now, brilliant!