using functions as criteria in a query

[garfieldsevilla]

I have created a query in Access 2003 as follows:

WHERE (((AccDS1check) Is Not Null) AND ((AccFabDate)=GetFDate()) AND ((AccRelacion)=21))

AccFabDate is a date value and GetFabDate is a function thus:

Public Function GetFabDate () As String
GetFabDate = ">#01/01/1980# and < #01/01/2011#”
End Function

When I run the query with ((AccFabDate)= ">#01/01/1980# and < #01/01/2011#”) there is no problem but when I use the public function to define the query, Access 2003 either crashes or returns.

Error 3464: mismatch in criteria expression. The criteria expression in a Find method is attempting to compare a field with a value whose data type does not match the field’s data type.

Can anyone help? I need to be able to change the query value dynamically and have code to calculate the string values in the public function

[ADezii]

I've never seen a Function used in this manner, to return a Criteria String, and I'm not even sure if it will work. What I am sure will work is to:

1. Pass the Value of your Date Field to a Public Function.
2. Have the Function return a Boolean Value indicating whether or not the Date Argument is within a specified Range.
3. As an additional Criteria, return all Records where the Return Value of the Function is True (within Range). It must also, of course, meet any additional Criteria because of the 'ANDs'.

WHERE ([AccDS1check] Is Not Null) AND ([AccRelacion] = 21)  AND (GetFabDate([AccFabDate]) = True)

Public Function GetFabDate(dteDate As Date) As Boolean
  GetFabDate = (dteDate >= #1/1/1980# And dteDate <= #1/1/2011#)
End Function

P.S. - I'm making the Assumptions that [AccFabDate] cannot be NULL, and is a Valid Date.

[NeoPa]

The function GetFabDate() (or maybe GetFDate() depending on what we look at) returns a value. It is not ever used in your example to formulate the SQL code.

I think you probably want the simpler Between construct :

WHERE (([AccDS1check] Is Not Null)
  AND  ([AccFabDate] Between #1/1/1980# And #12/31/2010#
  AND  ([AccRelacion]=21))