By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,078 Members | 1,317 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,078 IT Pros & Developers. It's quick & easy.

Type mismatch within user function

P: 4
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.

Expand|Select|Wrap|Line Numbers
  1. Function AgeGroup(aDate As Date) As String
  3. 'This function takes an argument aDate as athlete's date of birth and looks up the textual age group
  5. Dim tempvar As String
  6. Dim qdf As QueryDef
  7. Dim rst As Recordset
  8. Dim qdf1 As QueryDef
  9. Dim rst1 As Recordset
  11. 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];);")
  12. qdf.Parameters("Value") = (aDate)
  13.   Set rst = qdf.OpenRecordset
  14.  If rst.RecordCount > 0 Then tempvar = rst!AgeGroup
  15.    rst.Close
  17. AgeGroup = tempvar
  18. End Function
A couple of hours of looking at this has not enlightened me!

Any thoughts greatly appreciated.

Aug 2 '09 #1
Share this Question
Share on Google+
7 Replies

Expert 5K+
P: 8,679
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:
    Expand|Select|Wrap|Line Numbers
    1. Dim strRetVal As String      'Return Value should be a String
    3. 'Try passing Explicit Dates and see what happens
    4. strRetVal = AgeGroup(#12/30/2000#)
    5. strRetVal = AgeGroup(CDate(Me![txtDOB]))
    7. MsgBox "The appropriate Age Group is: " & strRetVal
Aug 2 '09 #2

P: 4
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 Sub

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.

Aug 2 '09 #3

Expert 5K+
P: 8,679
  1. Try changing the Return Type of the Function to Variant, either:
    Expand|Select|Wrap|Line Numbers
    1. Function AgeGroup(aDate As Date) 
    2.               OR
    3. Function AgeGroup(aDate As Date) As Variant
  2. Qualify your Declarations with the appropriate Type Library:
    Expand|Select|Wrap|Line Numbers
    1. Dim tempvar As String
    2. Dim qdf As DAO.QueryDef
    3. Dim rst As DAO.Recordset
    4. Dim qdf1 As DAO.QueryDef
    5. Dim rst1 As DAO.Recordset
Aug 2 '09 #4

P: 4
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.
Aug 2 '09 #5

Expert 5K+
P: 8,679
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...
Expand|Select|Wrap|Line Numbers
  1. 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.
Aug 2 '09 #6

P: 4
Brilliant - many thanks for your time.
Aug 2 '09 #7

Expert 5K+
P: 8,679
You are quite welcome.
Aug 2 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.