Iif statment in module

[lawtonl]

Hello,

I'm writing my first module ever and I'm a bit stuck.

I'm having to put this in a module because the IIF I was using within a query was too long, I assume a module is my only choice?

Anyway here's the code I have so far but it returns "Data Type Mismatch in Criteria Expression"

Function SIC(SICCode As Integer)
    SIC = IIf(SICCode >= 22000 And SICCode < 23000 Or SICCode >= 92000 And SICCode < 93000, "Media & Entertainment")
    SIC = IIf(SICCode >= 15000 And SICCode <= 21999 Or SICCode >= 23000 And SICCode < 38000, "Manufacturing")
End Function

[SICCode is a number field and I have Expr1: SIC([SICCode]) in the query

Any help would be greatly appreciated.

TIA

 

[DCrake]

You have not constructed you function correctly

Firstly the function must return a value and it is best to declare it as a public function

[B]Public [/B]Function [COLOR="Blue"]SIC[/COLOR]([COLOR="Red"]AnyCode [/COLOR]As Intger) [B]As String[/B]

Next use a Select Case Statement it is far easier to read and manage

Select Case [COLOR="red"]AnyCode[/COLOR]
     Case < 15000 : [COLOR="blue"]SIC [/COLOR]= "Unknown"
     Case < 22000 : [COLOR="blue"]SIC [/COLOR]= "Manufacturing"
     Case < 23000 : [COLOR="blue"]SIC [/COLOR]= "Media & Entertainment"
     Case < 38000 : [COLOR="blue"]SIC [/COLOR]= "Manufacturing"
     Case < 92000 : [COLOR="blue"]SIC [/COLOR]= "Unknown"
     Case < 93000 : [COLOR="blue"]SIC [/COLOR]= "Media & Entertainment"
     Case Else : [COLOR="blue"]SIC [/COLOR]= "Indeterminable"
End Select

End Function

You seem to have gaps

Less than 15000
between 38000 and 92000
Over 93000

David

 

[jal]

Function SIC(SICCode As Integer)
SIC = IIf(SICCode >= 22000 And SICCode < 23000 Or SICCode >= 92000 And SICCode < 93000, "Media & Entertainment")
SIC = IIf(SICCode >= 15000 And SICCode <= 21999 Or SICCode >= 23000 And SICCode < 38000, "Manufacturing")
End Function

If that's your IIF statement, I hardly think it's too long. You might want to change the first line to:

Function SIC(SICCode As Integer) as String

Secondly, you don't need an IIF in a function because a function can use ordinary IF-Then-Else-EndIF blocks which are easier to read and work with.

Thirdly use parantheses to clarify your logic.

For example

If (SICCode >= 15000 And SICCode <= 21999) _
Or (SICCode >= 23000 And SICCode < 38000) Then
Sic = "Manufacturing"
Exit function
Else if Sic >= 50,0000 Then
Sic = "Whatever"
exit function
Else
Sic = "WhateverWhatever"
End if

End function

 

[lawtonl]

Thanks for the quick responses.

That's just the start of what I'm doing hence the gaps in SIC's and the somewhat shorter code. I might also add that this is working from a linked excel file and I'm not sure whether that makes a difference or not.

I'm still getting the same type of error though

Using the code almost identical to what DCrake put

Public Function SIC(SICCode As Integer) As String
 
    Select Case SICCode
     Case Is < 15000: SIC = "Manufacturing"
     Case Is < 22000: SIC = "Manufacturing"
     Case Is < 23000: SIC = "Media & Entertainment"
     Case Is < 38000: SIC = "Manufacturing"
     Case Is < 92000: SIC = "Unknown"
     Case Is < 93000: SIC = "Media & Entertainment"
     Case Else: SIC = "Indeterminable"
 
End Select
End Function

 

[DCrake]

How are you passing the value to the function?
how are you calling the function?

Tip!

Press Ctrl+G to open immediate window

Enter:
?SIC(16000)

What happens?

David

 

[lawtonl]

It returns

?SIC(16000)
Manufacturing

However when I type ?SIC(44000) it returns

'runtime error 6'
Overflow

Thanks

 

[DCrake]

Then that is telling you that the function is syntactically correct. Therefore it must be the data you are passingto the function in the first place.

Check that it is actually a nueric value. and that it is an integer.

David

 

[JANR]

Public Function SIC(SICCode As Integer) As String

Shoulden't this be:

Public Function SIC(SICCode As [COLOR=red]Long[/COLOR]) As String

That will stop the overflow error.

JR

 

[DCrake]

Janr

Where did the overflow come into this?

 

[JANR]

It returns

?SIC(16000)
Manufacturing

However when I type ?SIC(44000) it returns

'runtime error 6'
Overflow

Thanks

Post # 6

JR

 

[DCrake]

Yes you are correct, I forgot to test for high values. I was pursueded by the initial attemp by the OP declaration.

It just seems now that we need some feedback from the OP.

David

 

[lawtonl]

I've imported the excel file as a table and the SIC Code field is being imported as a Long Integer. I've changed the syntax to

Public Function SIC(SICCode As Long) As String
 
    Select Case SICCode
     Case Is < 15000: SIC = "Manufacturing"
     Case Is < 22000: SIC = "Manufacturing"
     Case Is < 23000: SIC = "Media & Entertainment"
     Case Is < 38000: SIC = "Manufacturing"
     Case Is < 92000: SIC = "Unknown"
     Case Is < 93000: SIC = "Media & Entertainment"
     Case Else: SIC = "Indeterminable"
 
End Select
End Function

That fixes the Overflow issue in the immediate screen but I after rerunning the query but it still returns data type mismatch error.

 

[lawtonl]

here's the full SQL too in case that's of any use....

TRANSFORM Count([Enhanced Companies Data Send Table].SICCode) AS CountOfSICCode
SELECT [AccountsAll Accounts].[Cust Serv Region]
FROM [Enhanced Companies Data Send Table] INNER JOIN [AccountsAll Accounts] ON [Enhanced Companies Data Send Table].[Oracle Party Number] = [AccountsAll Accounts].[Party Number]
GROUP BY [AccountsAll Accounts].[Cust Serv Region]
PIVOT SIC([siccode]);

Also, is it likely to make a difference if some of the fields are null?

 

[DCrake]

Try creating a sub query that converts the SIC value to a description then use this query to base you pivot (cross tab query) on.

David

 

[DCrake]

As an afterthought, are you sure it is the SIC value that is causing the problem? Could there be fields with null values or values that do not evaluate correctly?

David

 

[lawtonl]

DCrake,

I think it might be something to do with null values, is there any way round that?

As for the SIC descriptions, they don't all match what we need them to fall into so I'm breaking it down my end.

 

[lawtonl]

It's sorted, it was down to null values, I ran an update query to set nulls to 0 and it works now.

Thank you both for your help!

 

[DCrake]

For future reference if you wrap Nz() around the field it will prevent further failures

SIC(Nz([SicCode],0))

David