CODEWITHSUNDEEP
ms-accessms-access-2010
ranopano
ranopano

Reputation: 539

Avoiding #Func! Error in Access Query

I have a field in Access that is formatted like this: Los Angeles, CA #00001

I need to modify the text field to look like this: #001 - Los Angeles, CA

To do this, I'm using this formula:

 "#" & Format([STORE NUMBER],"000") & " - " & Trim(Left([STORE NAME],InStr(1,[STORE NAME],"#")-1))

The problem I am running into is that not every record in my data set is formatted the same. There are a few instances where the location name is: "Test Store for California".

If this is the case, I get a "#Func!" error, likely because there is no "#" sign to look for to apply the formula.

Can anyone assist in finding a workaround to avoid this error.

Ideally I would like it so that if the the ouput does result in a "#Func!" error, just to use the original name like so:

+------------------------+------------------------+
|        Original        |          New           |
+------------------------+------------------------+
| Los Angeles, CA #00001 | #001 - Los Angeles, CA |
| Test Store for CA      | Test Store for CA      |
| San Diego, CA #00002   | #002 - San Diego, CA   |
+------------------------+------------------------+

Upvotes: 1

Views: 4205

Answers (2)

YowE3K
YowE3K

Reputation: 23974

You can just append a "#" to the end of the string prior to searching for a "#" using InStr:

"#" & Format([STORE NUMBER],"000") & " - " & Trim(Left([STORE NAME],InStr(1,[STORE NAME]&"#","#")-1))

If a "#" already exists, the InStr will find the location of the existing one. If one doesn't exist then InStr(1,[STORE NAME]&"#","#")-1 will just return the length of [STORE NAME] and therefore the Left will just return the entire field.

Upvotes: 2

Kostas K.
Kostas K.

Reputation: 8518

You could have a Function return the formatted value and if # does not exist, return the original value.

Public Function SplitStore(ByVal value_ As String) As String

    If InStr(1, value_, "#") = 0 Then
        SplitStore = value_
        Exit Function
    End If

    Dim arr As Variant
        arr = Split(value_, "#")

    SplitStore = "#" & Format(arr(1), "000") & " -" & arr(0)
End Function

To call it:

SELECT SplitStore([STORE NUMBER]) As FormattedStore
...


'#001 -Los Angeles, CA

Upvotes: 2

Related Questions