Passing a function value to a query

[Guest]

I have a form built on a query. I would like to allow the user to filter the
dates of the records returned so I offer them a set of unbound date controls
with default values of startDate = 10/01/2004 and endDate = 12/31/2200. The
criteria I am using in my query for the field to be filtered is a call to the
function passing three arguments (a boolean yes or no, the start date and the
end date):

DateFilter([forms]![frmReportSwitchboard]![chkDateYesNo],[forms]![frmReportSwitchboard]![dteStartDate],[forms]![frmReportSwitchboard]![dteEndDate])

The function i call is stored in a Module name basReportDateFilter and the
function is

Public Function DateFilter(chkDate As Boolean, dStart As Date, dEnd As Date)

Dim tmpDateRange As String
'MsgBox ("chkDate is " & chkDate & "; Start Date is " & dStart & "; End
Date is " & dEnd)
If chkDate = True Then
tmpDateRange = "Between #" & dStart & "# And #" & dEnd & "#"
'MsgBox (tmpDateRange)
Else
tmpDateRange = "Between #10/1/2004# And #12/31/2200#"
End If

DateFilter = tmpDateRange
'MsgBox (DateFilter)
End Function

(The msgboxes are there to test that the values captured are expected

When I run this set up I get the "This expression is typed incorrectly or it
is too complex to be evaluated. . . ." error.

Any help?

 

[John Spencer (MVP)]

Your function returns a string to the query. You can't really do what you are
attempting the way you are doing it. Alternatively, you might try entering the
following criteria into your query.

Field: SomeDateField
Criteria: Between
IIF(ChkDate,Forms!frmReportSwitchboard!dteStartDate,#10/1/2004#) AND IIF(ChkDate,Forms!frmReportSwitchboard!dteEndDate,#12/31/2200#)

If, as I suspect, you are trying to ignore the values in the datefield if
ChkDate is not checked, then you may still have a problem. This will not return
any records where SomeDateField is null (blank).

Do you have any fields that are blank? How do you want this situation handled
with blank fields?