Passing a function value to a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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?
 
Back
Top