Type mismatch within user function

[carl1957]

Hi. I'm using a function which I wrote (based on a forum reply to someone else). It did work fine but I've just come back to it after a few days and other coding, and now it is generating a Type Mismatch error.

All it is trying to do is to find an age group value based on a date of birth (eg "U15", or "senior"). The base table has fields: agegroup_id : auto, agegroup : text, agegroup_oldest : date/time.

Function AgeGroup(aDate As Date) As String
 
'This function takes an argument aDate as athlete's date of birth and looks up the textual age group
 
Dim tempvar As String
Dim qdf As QueryDef
Dim rst As Recordset
Dim qdf1 As QueryDef
Dim rst1 As Recordset
 
Set qdf = CurrentDb.CreateQueryDef("", "SELECT T1.agegroup FROM t_agegroup AS T1 WHERE T1.agegroup_oldest =(SELECT Min(T2.agegroup_oldest)FROM t_agegroup AS T2 WHERE T2.agegroup_oldest>=[value];);")
qdf.Parameters("Value") = (aDate)
  Set rst = qdf.OpenRecordset
 If rst.RecordCount > 0 Then tempvar = rst!AgeGroup
   rst.Close
 
AgeGroup = tempvar
End Function

A couple of hours of looking at this has not enlightened me!

Any thoughts greatly appreciated.

Thanks

[ADezii]

@carl1957
The code appears to be working just fine:

1. Are you Declaring a Variable as a String to accept the Return Value of the Function?
2. Try passing an Explicit Date to the AgeGroup() Function.
3. Both points are illustrated below:
   

   Dim strRetVal As String      'Return Value should be a String
    
   'Try passing Explicit Dates and see what happens
   strRetVal = AgeGroup(#12/30/2000#)
   strRetVal = AgeGroup(CDate(Me![txtDOB]))
    
   MsgBox "The appropriate Age Group is: " & strRetVal

[carl1957]

Hi. Thanks for quick reply. Yes, had tried that with same result. Test code is:

Private Sub Command13_Click()
On Error GoTo Err_Command13_Click

Dim xx As String

xx = AgeGroup(#11/1/1989#)
MsgBox xx

Exit_Command13_Click:
Exit Sub

Err_Command13_Click:
MsgBox Err.Description
Resume Exit_Command13_Click
End Sub

I'm sure it must be something obvious since I am sure I didn't change it between when it worked and when it didn't!

Have also tried embedding a date within the function and taking out the final assignment. Same result.

Cheers

[ADezii]

@carl1957

1. Try changing the Return Type of the Function to Variant, either:
   

   Function AgeGroup(aDate As Date) 
                 OR
   Function AgeGroup(aDate As Date) As Variant
    

2. Qualify your Declarations with the appropriate Type Library:
   

   Dim tempvar As String
   Dim qdf As DAO.QueryDef
   Dim rst As DAO.Recordset
   Dim qdf1 As DAO.QueryDef
   Dim rst1 As DAO.Recordset

[carl1957]

Hi. Thanks. Specifying DAO solved it. Why would I need to do that? Would the format have been different if ADO used?

Again thanks - appreciated.

[ADezii]

@carl1957
You probably have References set to both the DAO and ADO Object Libraries, in which case you should 'always' qualify the Library to which each Object belongs to avoid any potential Errors and ambiguity. If you do not qualify them, then the first Type Library listed in the References Dialog will be used, which you may/may not want.

P.S. - In your specific case, I would venture that the Reference to ADO is set prior to DAO. When the Compiler reaches this line of code...

Dim qdf As QueryDef

you will get a Type mismatch, since it will use the Higher Priority ADO Library which contains no such QueryDef animal.

[carl1957]

Brilliant - many thanks for your time.

[ADezii]

@carl1957
You are quite welcome.