CODEWITHSUNDEEP
sqlms-access
Adrian Chrostowski
Adrian Chrostowski

Reputation: 434

#Func! Error on iif query in MS Access

Below is my sql query:

IIf(remedy_src.Position Is Null,(mid(remedy_src.User,instr(1,remedy_src.User,"(")+1,instr(1,remedy_src.User,")")-2-instr(1,remedy_src.User,"(")+1)),remedy_src.Position) AS [Adjusted User]

The point is to extract string from a field. Here's an example of the value:

n123456 (name lastname)

the IIf function returns what is in the brackets:

name lastname

But. Sometimes the source value looks like that:

n123456

No brackets, and the IIf returns the ugly #Func! error which prevents the query to be refreshed in my excel file (external data connection to access db).

I would like to handle this error somehow. Preferably to make the IIf function return raw source value if error is present.

Upvotes: 1

Views: 3312

Answers (3)

Adrian Chrostowski
Adrian Chrostowski

Reputation: 434

Here's how I got a solution for this issue, kind of completely reqwriting the code to exclude any finction to return errors.

 IIf(remedy_src.Position Is Null,replace(replace(right(remedy_src.User,len(remedy_src.User)-instr(1,remedy_src.User,' ')),'(',''),')',''),remedy_src.Position) AS [Adjusted User]

Upvotes: 0

Newd
Newd

Reputation: 2185

I think that this SQL string is getting a little too complex to easily work with. I have made a VBA function that should be able to do the same thing but with easily understandable and easier to work with.

The Code: 

Public Function ExtractString(str As String) As String

    Dim intFirstBracket As Integer
    Dim intSecondBracket As Integer
    Dim blnValidString As Boolean

    blnValidString = False

    If Nz(str, "") <> "" Then
        intFirstBracket = InStr(1, str, "(", vbTextCompare)
        If intFirstBracket > 0 Then
            intSecondBracket = InStr(1, str, ")", vbTextCompare)

            If intSecondBracket > 0 Then
                str = Mid(str, intFirstBracket + 1, intSecondBracket - (intFirstBracket + 1))
                blnValidString = True
            End If
        End If
    End If

    If blnValidString Then
        ExtractString= str
    Else
        ExtractString= "Default Value" 'Handle this how you want
    End If

End Function

So your SQL string would simply be: 

IIf(remedy_src.Position Is Null, ExtractString(remedy_src.User) ,remedy_src.Position) AS [Adjusted User]

The function I have tested, the SQL I have not so just take that into consideration.

Upvotes: 0

Gene
Gene

Reputation: 392

You could try to catch the error:

IIF(IsERROR(IIf(remedy_src.Position Is Null,(mid(remedy_src.User,instr(1,remedy_src.User,"(")+1,instr(1,remedy_src.User,")")-2-instr(1,remedy_src.User,"(")+1)),remedy_src.Position)),
remedy_src.user,
IIf(remedy_src.Position Is Null,(mid(remedy_src.User,instr(1,remedy_src.User,"(")+1,instr(1,remedy_src.User,")")-2-instr(1,remedy_src.User,"(")+1)),remedy_src.Position)) 
AS [Adjusted User]

or

IIF(InStr("(",remedy_src.user)=0,
remedy_src.user,
IIF(IsERROR(IIf(remedy_src.Position Is Null,(mid(remedy_src.User,instr(1,remedy_src.User,"(")+1,instr(1,remedy_src.User,")")-2-instr(1,remedy_src.User,"(")+1)),remedy_src.Position))
As [Adjusted User]

Upvotes: 1

Related Questions