functions in queries

[seeker]

I have inserted a function in the criteria of a date field in a query and it
returns a data mismath error. Following is the sql and the function code.

The SQL
SELECT member_time.member_number, tblMemberNameandAddress.FirstName,
tblMemberNameandAddress.LastName, tblMemberNameandAddress.Address,
tblMemberNameandAddress.City, tblMemberNameandAddress.State,
tblMemberNameandAddress.ZipCode, tblMemberNameandAddress.PhoneNumber,
tblMemberNameandAddress.AltPhoneNumber,
Sum(DateDiff("n",member_time!time_in,member_time!time_out)/60) AS hours
FROM member_time INNER JOIN tblMemberNameandAddress ON
member_time.member_number = tblMemberNameandAddress.MemberNumber
WHERE (((member_time.date)=criteriachange(1)))
GROUP BY member_time.member_number, tblMemberNameandAddress.FirstName,
tblMemberNameandAddress.LastName, tblMemberNameandAddress.Address,
tblMemberNameandAddress.City, tblMemberNameandAddress.State,
tblMemberNameandAddress.ZipCode, tblMemberNameandAddress.PhoneNumber,
tblMemberNameandAddress.AltPhoneNumber
ORDER BY tblMemberNameandAddress.LastName;

the function which is called:
Function criteriachange(a As Integer) As String
Select Case DatePart("m", Date)
Case 1
Select Case a
Case 1
criteriachange = "between #12/1/08# and #12/31/08#"
Case 2
criteriachange = "between #11/1/08# and #11/30/08#"
Case 3
criteriachange = "between #10/1/08# and #10/31/08#"
Case 4
criteriachange = "between #9/1/08# and #9/30/08#"
Case 5
criteriachange = "between #8/1/08# and #8/31/08#"
Case 6
criteriachange = "between #7/1/08# and #7/31/08#"
End Select
Case 2
Select Case a
Case 1
criteriachange = "between #1/1/09# and #1/31/09#"
Case 2
criteriachange = "between #12/1/08# and #12/31/08#"
Case 3
criteriachange = "between #11/1/08# and #11/30/08#"
Case 4
criteriachange = "between #10/1/08# and #10/31/08#"
Case 5
criteriachange = "between #9/1/08# and #9/30/08#"
Case 6
criteriachange = "between #8/1/08# and #8/31/08#"
End Select
Case 3
Select Case a
Case 1
criteriachange = "between #2/1/09# and #2/28/09#"
Case 2
criteriachange = "between #1/1/09# and #1/31/09#"
Case 3
criteriachange = "between #12/1/08# and #12/31/08#"
Case 4
criteriachange = "between #11/1/08# and #11/30/08#"
Case 5
criteriachange = "between #10/1/08# and #10/31/08#"
Case 6
criteriachange = "between #9/1/08# and #9/30/08#"
End Select
Case 4
Select Case a
Case 1
criteriachange = "between #3/1/09# and #3/31/09#"
Case 2
criteriachange = "between #2/1/09# and #2/28/09#"
Case 3
criteriachange = "between #1/1/09# and #1/31/09#"
Case 4
criteriachange = "between #12/1/08# and #12/31/08#"
Case 5
criteriachange = "between #11/1/08# and #11/30/08#"
Case 6
criteriachange = "between #10/1/08# and #10/31/08#"
End Select
Case 5
Select Case a
Case 1
criteriachange = "between #4/1/09# and #4/30/09#"
Case 2
criteriachange = "between #3/1/09# and #3/31/09#"
Case 3
criteriachange = "between #2/1/09# and #2/28/09#"
Case 4
criteriachange = "between #1/1/09# and #1/31/09#"
Case 5
criteriachange = "between #12/1/08# and #12/31/08#"
Case 6
criteriachange = "between #11/1/08# and #11/30/08#"
End Select
Case 6
Select Case a
Case 1
criteriachange = "between #5/1/09# and #5/31/09#"
Case 2
criteriachange = "between #4/1/09# and #4/30/09#"
Case 3
criteriachange = "between #3/1/09# and #3/31/09#"
Case 4
criteriachange = "between #2/1/09# and #2/28/09#"
Case 5
criteriachange = "between #1/1/09# and #1/31/09#"
Case 6
criteriachange = "between #12/1/08# and #12/31/08#"
End Select
Case 7
Select Case a
Case 1
criteriachange = "between #6/1/09# and #6/30/09#"
Case 2
criteriachange = "between #5/1/09# and #5/31/09#"
Case 3
criteriachange = "between #4/1/09# and #4/30/09#"
Case 4
criteriachange = "between #3/1/09# and #3/31/09#"
Case 5
criteriachange = "between #2/1/09# and #2/28/09#"
Case 6
criteriachange = "between #1/1/09# and #1/31/09#"
End Select
Case 8
Select Case a
Case 1
criteriachange = "between #7/1/09# and #7/31/09#"
Case 2
criteriachange = "between #6/1/09# and #6/30/09#"
Case 3
criteriachange = "between #5/1/09# and #5/31/09#"
Case 4
criteriachange = "between #4/1/09# and #4/30/09#"
Case 5
criteriachange = "between #3/1/09# and #3/31/09#"
Case 6
criteriachange = "between #2/1/09# and #2/28/09#"
End Select
Case 9
Select Case a
Case 1
criteriachange = "between #8/1/09# and #8/31/09#"
Case 2
criteriachange = "between #7/1/09# and #7/31/09#"
Case 3
criteriachange = "between #6/1/09# and #6/30/09#"
Case 4
criteriachange = "between #5/1/09# and #5/31/09#"
Case 5
criteriachange = "between #4/1/09# and #4/30/09#"
Case 6
criteriachange = "between #3/1/09# and #3/31/09#"
End Select
Case 10
Select Case a
Case 1
criteriachange = "between #9/1/09# and #9/30/09#"
Case 2
criteriachange = "between #8/1/09# and #8/31/09#"
Case 3
criteriachange = "between #7/1/09# and #7/31/09#"
Case 4
criteriachange = "between #6/1/09# and #6/30/09#"
Case 5
criteriachange = "between #5/1/09# and #5/31/09#"
Case 6
criteriachange = "between #4/1/09# and #4/30/09#"
End Select
Case 11
Select Case a
Case 1
criteriachange = "between #10/1/09# and #10/31/09#"
Case 2
criteriachange = "between #9/1/09# and #9/30/09#"
Case 3
criteriachange = "between #8/1/09# and #8/31/09#"
Case 4
criteriachange = "between #7/1/09# and #7/31/09#"
Case 5
criteriachange = "between #6/1/09# and #6/30/09#"
Case 6
criteriachange = "between #5/1/09# and #5/30/09#"
End Select
Case 12
Select Case a
Case 1
criteriachange = "between #11/1/09# and #11/30/09#"
Case 2
criteriachange = "between #10/1/09# and #10/31/09#"
Case 3
criteriachange = "between #9/1/09# and #9/30/09#"
Case 4
criteriachange = "between #8/1/09# and #8/31/09#"
Case 5
criteriachange = "between #7/1/09# and #7/31/09#"
Case 6
criteriachange = "between #6/1/09# and #6/30/09#"
End Select
End Select
End Function

What is causing the error??? Thanks

 

[John Spencer MVP]

You can't do it that way. You are passing back a string which is being
compared to a date.

Function CriteriaChange (A as Integer, B as Integer) as Date
'a is which case
'b is 1 or 2 and indicates which of two values to return

Select Case DatePart("m", Date)
Case 1
Select Case a
Case 1
If B = 1 then
criteriaChange = #2008-12-01#
Else
CriteriaChange = #2008-12-31#
Case 2
...
End Select
Case 2
....

End Function

The where clause would now read
WHERE member_time.date >= CriteraChange(1,1) and member_time.date <=
CriteriaChange(1,2)


However, there is a probably a much easier way to do this.

member_time.date Between
DateSerial(Year(Date()),Month(Date())-[How Many Months],1)
And DateSerial(Year(Date()), 1 + Month(Date())-[How Many Months],0)

So if How Many Months = 1 and the current month is April (4) that expression
should return
member_time.date Between #2009-03-01# and #2009-03-31

For 4 it should return and any date in april 2009 it will return
#2008-12-01# and #2008-12-31#

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County