Type mismatch running a query (using a module)

[istuart]

Please bear with me as I am still a novice.

I've created several modules in my database that when expressed in a query would "group" the data accordingly. It usually works like a charm. However, I get a "data type mismatch in criteria expression" error message for the following module when I use it in an expression in my query. I can't figure out why.


The module is set up as follows (below that is the query's sql):

Function JobGroup(Skill As String)
If Skill = "C/C++" Or Skill = "COBOL/CICS" _
Or Skill = "Middleware" Or Skill = "Motif" _
Or Skill = "Object Design" Or Skill = "OCS/2" _
Or Skill = "Paradox" Or Skill = "TAL" _
Or Skill = "Visual Basic" Then
JobGroup = CStr("Development&quot
ElseIf Skill = "Comm. Engineering" Or Skill = "LAN/WAN" _
Or Skill = "Comm. Technician" Or Skill = "Networking" Then
JobGroup = CStr("Network/Communications&quot
ElseIf Skill = "Administration" Or Skill = "Checkpoint" _
Or Skill = "DCE" Or Skill = "Gauntlet" Or Skill = "HP" _
Or Skill = "ISIS" Or Skill = "Sys Admin - UNIX" Then
JobGroup = CStr("Systems Administration&quot
ElseIf Skill = "Project Management" Or Skill = "Technical Writing" Then
JobGroup = CStr("Project Management&quot
ElseIf Skill = "Quality Assurance" Then
JobGroup = CStr("Quality Assurance&quot
Else
JobGroup = Null
End If
End Function

I then set up a query that would Group by Skill and includes skill, level, min. rate, max. rate, average rate and number of people in each group.

The sql for the query is as follows:

SELECT DISTINCTROW JobGroup([Skill]) AS [Job Group], [Consultants on Board].Skill, [By Grouping].Level, Avg([Consultants on Board].CurrentRate) AS [Avg Of Rate], Min([Consultants on Board].CurrentRate) AS [Min Of Rate Desired], Max([Consultants on Board].CurrentRate) AS [Msx Of Rate Desired], Count(*) AS [Count Of By Grouping]
FROM [Consultants on Board] INNER JOIN [By Grouping] ON [Consultants on Board].Name = [By Grouping].Name
GROUP BY JobGroup([Skill]), [Consultants on Board].Skill, [By Grouping].Level;

 

[MichaelRed]

My only real thought is the use ofcoercive type casting on the embeded string(s) is a bit much. On the other hand, the readability factor as presented is quite low, so I re-cast (pun intended) your procedure as a select case statement block and removed the coercion of literals to strings. I doubt it will work any better, but it might look better (after all. " ... beauty is in the eye ... &quot.

Function JobGroup(Skill As String)

    Select Case Skill

        Case Is = "C/C++", "COBOL/CICS", "Middleware", "Motif"
            JobGroup = "Development"

        Case Is = "Object Design", "OCS/2", "Paradox", "TAL", "Visual Basic"
            JobGroup = "Development"

        Case Is = "Comm. Engineering", "LAN/WAN", "Comm. Technician", "Networking"
            JobGroup = "Network/Communications"

        Case Is = "Administration", "Checkpoint", "DCE", "Gauntlet"
            JobGroup = "Systems Administration"

        Case Is = "HP", "ISIS", "Sys Admin - UNIX"
            JobGroup = "Systems Administration"

        Case Is = "Project Management", "Technical Writing"
            JobGroup = "Project Management"

        Case Is = "Quality Assurance"
            JobGroup = "Quality Assurance"

        Case Else
            JobGroup = "Quality Assurance"

    End Select

End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Umbane]

I'm not sure this will help, but you should check the following.

Even though the query results appear to be fine (Visually speaking ) you're probably using a combo box to select the skill in a form (or query), then hiding the value of the index so that the user sees the skill; however access still sees the value of that field as numeric and you're trying to compare it with a text value.
Example:
Table1 = Consultants
Field1 = ConsultantID (Autonumber)
Field2 = ConsultantName (Text)
Field3 = SkillID (Numeric)'Lookup from Table2 where you set the bound column to 1 and column count to 0cm;4cm;4cm

Table2 = Skills
Field1 = SkillsID (Autonumber)
Field2 = SkillText (Text)

If you open table1 and type in a consultant name and then use the combo box in Field3 to insert a skill you will notice that the skill text appears in the field. This is great but actually the value stored is the numeric number associated with that record in Table2.

Hope I haven't lost you yet!

When you try to automate as you are using a macro or vb and you refernce numeric data to compare to string data you will get the data type mismatch error.

Colin
colin@m5developments.co.za
Field2 = Consultant

 

[jebry]

Hi!

Another possible problem will occur if [Skill](i.e. the field in the query) is ever null. Since the function is expecting a string it will not accept a null value passed to it. To account for the possibility of a null, even if that isn't the cause of the present problem, then change the declaration to variant.

Public Function JobGroup(Skill as Variant)

hth Jeff Bridgham
bridgham@purdue.edu