calling a Function from strSQL getting type mismatch error

[Access05]

Hi,

I'm trying to pass this function to return a character string but I'm
getting a type mismatch error 13 I dumped the contents of BU and it
looks fine but as soon as I press further, I get this error.

Progress 1, "Adding new fields ..." ' and changing
YEARMON to a text field ..."

strSQL = "SELECT *, left(yearmon,4) as YR," _
& " GetBranch([BU]) AS BRANCH," <=== trying to
pass a field called BU
strSQL = strSQL & "IIf([team] In
('1','2','5','8',),'Specialized Claims Services'," _
& "IIf([team] In ('0', 'O'),'Health Services'," _
& "IIf([team] In ('6'),'Regulatory Claims
Officers'," _
& "IIf([team] In ('Z'),'Specialized Claims
Services'," _
& "IIf([district] In ('K','G'),'Service
Delivery'," _
& "IIf([team] In ('A'),'Service Delivery'," _
& "IIf([district] In
('U','M','S','Y','N'),'Service Delivery'," _
& "IIf([team] In ('D'),'Unknown/Other'," _
& "IIf([district] In ('T') and [team] In
('B','F','L','M','S','E','H','V','C','T','9'),'Service Delivery'," _
& "IIf([district] In
('Q','R','H','A','C','W','L'),'Service Delivery','Unknown/
Other')))))))))) AS DIVISION INTO " _
& dataTable & " FROM " & tempTable & ";"

'MsgBox strSQL
'CurrentDb.Execute strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
..
..
..


in Main pgm

Function GetBranch(BU as String) As String
Dim strDist, strTeam As String

MsgBox BU <== looks fine here

strDist = Right(BU, 1)
strTeam = Left(BU, 1)

MsgBox strDist, strTeam <== once I split them up, I get the type
mismatch 13 error ?

' Select Case Right(BU, 1) Or Left(BU, 1)
Select Case strDist Or strTeam
Case strDist = "1"
GetBranch = "Central Claims Processing"
Case strDist = "2"
GetBranch = "Admin Services"
Case strDist = "5"
GetBranch = "ISC"
Case strDist = "8"
GetBranch = "Pre-1990"
Case strDist = "5"
GetBranch = "ISC"
Case strDist = "O", "0"
GetBranch = "Occupational Disease & SBP"
Case strDist = "6"
GetBranch = "Regulatory Claims Officers"
Case strTeam = "K", "G"
GetBranch = "Kitchener/Guelph"
Case strDist = "A"
GetBranch = "Kitchener/Guelph"
Case strTeam = "U", "M"
GetBranch = "Thunder Bay/SSM"
Case strTeam = "S", "Y", "N"
GetBranch = "Sudbury/Timmins/North Bay"
Case strDist = "D"
GetBranch = "Unknown/Other"
Case strTeam = "T"
If strDist = "F" Or "L" Or "M" Then
GetBranch = "Industrial"
End If
Case strTeam = "T"
If (strDist = "S" Or "E") Then
GetBranch = "Government Services"
End If
Case strTeam = "T"
If (strDist = "H" Or "V") Then
GetBranch = "Services & Health Care"
End If
Case strTeam = "T"
If (strDist = "C" Or "T") Then
GetBranch = "Transportation & Construction"
End If
Case strTeam = "T"
If (strDist = "9") Then
GetBranch = "Small Business"
End If
Case strTeam = "Q", "R"
GetBranch = "Ottawa/Kingston"
Case strTeam = "H", "A", "C"
GetBranch = "Hamilton/St. Catherines"
Case strTeam = "W"
GetBranch = "Windsor"
Case strTeam = "L"
GetBranch = "London"
Case Else
GetBranch = "Unknown/Other"
End Select

End Function

Any suggestions?

Thanks,
Rose

 

[Graham Mandeno]

Hi Rose

MsgBox strDist, strTeam <== once I split them up, I get the type
mismatch 13 error ?

The MsgBox function takes several arguments (five to be exact) of which all
but the first are optional. The first argument (required) is the text
message to display (you are passing strDist, which is fine). The second
argument is to specify options such as which buttons and which icon to
display. These options are encoded in a single integer number. You are
passing a text string (strTeam) when it expects a number, hence the type
mismatch.

If you want to include several different parts in the message string, you
must build them into a single string. For example:

MsgBox "strDist is " & strDist & " and strTeam is " & strTeam

Select Case strDist Or strTeam

This is also likely to give you a type mismatch, unless both strDist and
strTeam contain numeric characters, and even then it will not do what you
want. Select Case cannot be used to make a selection on two variables at
once.

You could use a trick to do the select case "in reverse". Instead of
selecting on an expression and finding the first constant that matches it,
you select on a constant, True, and find the first expression that matches
it:

Select Case True
Case strDist = "1"
GetBranch = "Central Claims Processing"
Case strDist = "2"
GetBranch = "Admin Services"
Case strTeam = "K" Or strTeam = "G"
GetBranch = "Kitchener/Guelph"
.... etc

It really looks to me like you would be much better off with two lookup
tables - one for districts and one for teams - to translate the code
characters into branch names. It would also be a whole lot easier to change
the records in your lookup tables than to edit your code every time there
was a change in the branches.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi,

I'm trying to pass this function to return a character string but I'm
getting a type mismatch error 13 I dumped the contents of BU and it
looks fine but as soon as I press further, I get this error.

Progress 1, "Adding new fields ..." ' and changing
YEARMON to a text field ..."

strSQL = "SELECT *, left(yearmon,4) as YR," _
& " GetBranch([BU]) AS BRANCH," <=== trying to
pass a field called BU
strSQL = strSQL & "IIf([team] In
('1','2','5','8',),'Specialized Claims Services'," _
& "IIf([team] In ('0', 'O'),'Health Services'," _
& "IIf([team] In ('6'),'Regulatory Claims
Officers'," _
& "IIf([team] In ('Z'),'Specialized Claims
Services'," _
& "IIf([district] In ('K','G'),'Service
Delivery'," _
& "IIf([team] In ('A'),'Service Delivery'," _
& "IIf([district] In
('U','M','S','Y','N'),'Service Delivery'," _
& "IIf([team] In ('D'),'Unknown/Other'," _
& "IIf([district] In ('T') and [team] In
('B','F','L','M','S','E','H','V','C','T','9'),'Service Delivery'," _
& "IIf([district] In
('Q','R','H','A','C','W','L'),'Service Delivery','Unknown/
Other')))))))))) AS DIVISION INTO " _
& dataTable & " FROM " & tempTable & ";"

'MsgBox strSQL
'CurrentDb.Execute strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
.
.
.


in Main pgm

Function GetBranch(BU as String) As String
Dim strDist, strTeam As String

MsgBox BU <== looks fine here

strDist = Right(BU, 1)
strTeam = Left(BU, 1)

MsgBox strDist, strTeam <== once I split them up, I get the type
mismatch 13 error ?

' Select Case Right(BU, 1) Or Left(BU, 1)
Select Case strDist Or strTeam
Case strDist = "1"
GetBranch = "Central Claims Processing"
Case strDist = "2"
GetBranch = "Admin Services"
Case strDist = "5"
GetBranch = "ISC"
Case strDist = "8"
GetBranch = "Pre-1990"
Case strDist = "5"
GetBranch = "ISC"
Case strDist = "O", "0"
GetBranch = "Occupational Disease & SBP"
Case strDist = "6"
GetBranch = "Regulatory Claims Officers"
Case strTeam = "K", "G"
GetBranch = "Kitchener/Guelph"
Case strDist = "A"
GetBranch = "Kitchener/Guelph"
Case strTeam = "U", "M"
GetBranch = "Thunder Bay/SSM"
Case strTeam = "S", "Y", "N"
GetBranch = "Sudbury/Timmins/North Bay"
Case strDist = "D"
GetBranch = "Unknown/Other"
Case strTeam = "T"
If strDist = "F" Or "L" Or "M" Then
GetBranch = "Industrial"
End If
Case strTeam = "T"
If (strDist = "S" Or "E") Then
GetBranch = "Government Services"
End If
Case strTeam = "T"
If (strDist = "H" Or "V") Then
GetBranch = "Services & Health Care"
End If
Case strTeam = "T"
If (strDist = "C" Or "T") Then
GetBranch = "Transportation & Construction"
End If
Case strTeam = "T"
If (strDist = "9") Then
GetBranch = "Small Business"
End If
Case strTeam = "Q", "R"
GetBranch = "Ottawa/Kingston"
Case strTeam = "H", "A", "C"
GetBranch = "Hamilton/St. Catherines"
Case strTeam = "W"
GetBranch = "Windsor"
Case strTeam = "L"
GetBranch = "London"
Case Else
GetBranch = "Unknown/Other"
End Select

End Function

Any suggestions?

Thanks,
Rose

 

[Access05]

Thank you so much, Graham!
I used your suggestion of using the select case "in reverse" using
True instead.... it worked like a charm. About moving these variables
into tables, I was asked to trouble shoot this for a colleague who is
on sick leave. I will leave your suggestion for my colleague whenever
he comes back as I hate to mess with other people's stuff and vica
versa. I was pressed to get this out by end of day (last) Friday --
I've been looking at this way too long and finally decided to post
late last night....I'm sooo glad I did! I appreciate your quick
response and this forum as there is alot of valuable information.

Have a wonderful day,
Rose