Problems passing text box data to a function via a query

[Derevon]

Hello,

I have a function in a module that looks like this (it takes 2 dates as arguments):

Function get_KPIScanAgeRange(in_ScanDate As Date, KPIDate As Date) As String
Dim ret As String
ret = "Invalid"
    ' return value, by default is because age is negative number
 
Dim int_ScanAge As Integer
int_ScanAge = DateDiff("d", in_ScanDate, KPIDate)
If int_ScanAge >= 0 And int_ScanAge <= 15 Then ret = "0-15 Days"
If int_ScanAge >= 16 And int_ScanAge <= 30 Then ret = "16-30 Days"
If int_ScanAge >= 31 And int_ScanAge <= 45 Then ret = "31-45 Days"
If int_ScanAge >= 46 And int_ScanAge <= 60 Then ret = "46-60 Days"
If int_ScanAge > 60 Then ret = "Over 60 Days"
' puts age into appropriate range
get_KPIScanAgeRange = ret
End Function

I'm trying to pass the date in a text box which exists in a form called "d3FormAging". The text box name is "KPIDate". The query looks like this:

SELECT Query_d3_Open.[Company No], get_KPIScanAgeRange([Scan date],[Forms]![d3FormAging]![KPIDate]) AS KPIScanAgeRange, Count(Query_d3_Open.[Scan date]) AS Scans
FROM Query_d3_Open
GROUP BY Query_d3_Open.[Company No], get_KPIScanAgeRange([Scan date],[Forms]![d3FormAging]![KPIDate])
ORDER BY Query_d3_Open.[Company No], get_KPIScanAgeRange([Scan date],[Forms]![d3FormAging]![KPIDate]);

For some reason Access doesn't recognize the "[Forms]![d3FormAging]![KPIDate]" when I pass it to the function. I get run-time error 3070 with information that Access can't recognize this expression even though the code should be correct as it's made with the expression builder and I'm sure the form that contains the text box is open when the query runs. Anyone has any idea what could be wrong?

Thanks

 

[JHB]

Try to convert the control value in the form to a datevalue when you call the function.

DateValue([Forms]![d3FormAging]![KPIDate])

 

[Derevon]

As it turns out, it was a crosstab query using the previously mentioned query that failed, not the one I pasted in the previous post (my mistake). By manually adding [Forms]![d3FormAging]![KPIDate] with datatype "Date" in the crosstab query parameters in accordance with the following link, it turned out to work just fine:

http://support.microsoft.com/kb/209778/en-us