Is it possible to use a function to set query criteria

[Dale]

I have an inhouse function:
Function CalRunDate(drawn_date As Date)
Dim iday As Integer, calrundate2
iday = Weekday(DMax("drawn_date", "tblInLab_WC_TAT_ER57"))
Select Case iday
Case 6
CalRunDate = DateAdd("d", -4, (DMax("drawn_date", "tblInLab_WC_TAT_ER57")))
calrundate2 = DateAdd("d", 0, (DMax("drawn_date", "tblInLab_WC_TAT_ER57")))
CalRunDate = "Between " & Chr(35) & CalRunDate & Chr(35) & " and " & Chr(35)
& calrundate2 & Chr(35)
Case 1
CalRunDate = DateAdd("d", -6, (DMax("drawn_date", "tblInLab_WC_TAT_ER57")))
calrundate2 = DateAdd("d", -2, (DMax("drawn_date", "tblInLab_WC_TAT_ER57")))
CalRunDate = "Between " & Chr(35) & CalRunDate & Chr(35) & " and " & Chr(35)
& calrundate2 & Chr(35)
Case Else
Exit Function
End Select
End Function

Trying to call this from the criteria in a query grid of a query, but I keep
getting data type mismatch error. Can someone tell me if this is even
doable? I can get it to work without the "between .. and", by searching on
one date so I'm thinking its my syntax.

Thanks

 

[John W. Vinson]

I have an inhouse function:
Function CalRunDate(drawn_date As Date)
Dim iday As Integer, calrundate2
iday = Weekday(DMax("drawn_date", "tblInLab_WC_TAT_ER57"))
Select Case iday
Case 6
CalRunDate = DateAdd("d", -4, (DMax("drawn_date", "tblInLab_WC_TAT_ER57")))
calrundate2 = DateAdd("d", 0, (DMax("drawn_date", "tblInLab_WC_TAT_ER57")))
CalRunDate = "Between " & Chr(35) & CalRunDate & Chr(35) & " and " & Chr(35)
& calrundate2 & Chr(35)
Case 1
CalRunDate = DateAdd("d", -6, (DMax("drawn_date", "tblInLab_WC_TAT_ER57")))
calrundate2 = DateAdd("d", -2, (DMax("drawn_date", "tblInLab_WC_TAT_ER57")))
CalRunDate = "Between " & Chr(35) & CalRunDate & Chr(35) & " and " & Chr(35)
& calrundate2 & Chr(35)
Case Else
Exit Function
End Select
End Function

Trying to call this from the criteria in a query grid of a query, but I keep
getting data type mismatch error. Can someone tell me if this is even
doable? I can get it to work without the "between .. and", by searching on
one date so I'm thinking its my syntax.

Thanks

If you're calling CalRunDate as a criterion of the query, It Won't Work. You
can pass *values* in a criterion, but you're not passing values, you're
actually constructing part of the SQL string.

I'd suggest having two functions, one for the start date and one for the end
date; specify that they are of Date type

Public Function CalStartDate(drawn_date as Date) As Date
<calculate the from date>
End Function

and then use

BETWEEN CalStartDate(drawn_date) And CalEndDate(drawn_date)

as your criterion.