user designed function not found

[wa7gh]

I have written a function that uses a user defined type. Here is the type

Option Compare Database
 
Public Type ProcessGender
   additionalFemales As Double
   additionalMales As Double
 End Type

The function looks like this.

Option Compare Database
Option Explicit
 
 
Public Function genderCount(ethnicity As String, awardLevel As String) As ProcessGender
 
    Dim db As Database
    Dim rs As DAO.Recordset
    Dim strSQLString As String
    Dim iFemaleCount As Double
    Dim iMaleCount As Double
    Dim iTotalCount As Double
    Dim iUnknownCount As Integer
    Dim iNewFemaleCount As Long
    Dim iNewMaleCount As Double
    Dim dPercentFemale As Double
 
 
    Set db = CurrentDb()
    strSQLString = "SELECT COUNT(*) FROM [CStep  9:  Completers By Level] WHERE sex =  ""F"" "
    strSQLString = strSQLString + " AND converted_Eth_Orig = " '" + ethnicity + "' "
    If CInt(awardLevel) > 0 Then
       strSQLString = strSQLString + " AND award_level = " '" + awardLeve + "' "
    End If
    Set rs = db.OpenRecordset(strSQLString)
    rs.MoveFirst
    iFemaleCount = rs.Fields(0)
    rs.Close
    strSQLString = "SELECT COUNT(*) FROM [Step  9:  Completers By Level] WHERE sex = ""M"" "
    strSQLString = strSQLString + " AND converted_Eth_Orig = " '" + ethnicity + "' "
    If CInt(awardLevel) > 0 Then
       strSQLString = strSQLString + " AND award_level = " '" + awardLeve + "' "
    End If
    Set rs = db.OpenRecordset(strSQLString)
    iMaleCount = rs.Fields(0)
    rs.Close
    strSQLString = "SELECT COUNT(*) FROM [Step  9:  Completers By Level] WHERE sex IS NULL "
    strSQLString = strSQLString + " AND converted_Eth_Orig = " '" + ethnicity + "' "
    If CInt(awardLevel) > 0 Then
       strSQLString = strSQLString + " AND award_level = " '" + awardLeve + "' "
    End If
    Set rs = db.OpenRecordset(strSQLString)
    rs.MoveFirst
    iUnknownCount = rs.Fields(0)
    rs.Close
    iTotalCount = iFemaleCount + iMaleCount + iUnknownCount
    dPercentFemale = iFemaleCount / iTotalCount
    iNewFemaleCount = iUnknownCount * dPercentFemale
    iNewFemaleCount = CInt(iNewFemaleCount * 10) / 10
    iNewMaleCount = iUnknownCount - iNewFemaleCount
    db.Close
 
    genderCount.additionalFemales = iNewFemaleCount
    genderCount.additionalMales = iNewMaleCount
 
End Function

and I'm calling it like this

SELECT Title, AYR, sex, ipeds_title, 
SWITCH
( 
   sex = "M", COUNT(dw_key) + [GenderCount(converted_eth_orig)].additionalMale,
   sex = "F",  COUNT(dw_key) + [GenderCount(converted_eth_orig)].additionalFemale
) AS TotalCount
FROM [Step  9:  Completers By Level] AS c INNER JOIN (SELECT DISTINCT ipedsRaceCode, ipeds_title FROM newRaceCodes)  AS r ON r.ipedsRaceCode = c.converted_eth_orig
GROUP BY Title, ayr, ipeds_title, sex
ORDER BY Title, ayr, ipeds_title, sex;

When I try to run the query I get "Enter Parameter Value" box specifying the function call

For testing I created another module with the same code without the parameters. I hard coded those variables in my test code. It compiles and runs.

Then I created a macro to run the function. When I select RUNCODE and am prompted for the function to run, my function name comes up in the drop down box, so at that point Access can see the function.

When I try to run the macro I get "The expression you entered has a function name that Microsoft Access can't find.

I have renamed my module and function name to make sure there aren't any conflicts that I can see. I've tried doing a compact/repair and decompiling the database.
I am running in Access 2010 using an accdb file.

I have spent my afternoon Googling for a solution, and tried everything I have found.

I would very much appreciate any suggestions.

Thanks in advance

[Rabbit]

Please use code tags when posting code.

Don't put those square brackets around the function call. When you put square brackets around something, it demarcates it as a field or table name, it's not going to see it as a function.

[wa7gh]

Thanks Rabbit. I only put those around to get past another error. Without the square brackets I am getting

Invalid use of '.', '!', or '()'. in query expression 'GenderCount(converted_eth_orig,"0").additionalMal e'

.additionalMale gets populated in the function. It is a member of a user defined type I created so I could return two values. Maybe I will try passing in the gender and having the function just return an integer.

[Rabbit]

After some testing, I think you may have to do just that. You could use an additional parameter to tell the function which of the two values to return.

[wa7gh]

That's what I'm doing and it is running. I think it is going to be slow, but at least I will get the answer.

Thanks Rabbit.