Access Query Expression Giving !Func# Error

Question

Folks,

  Can someone please help me.  I have the following expression giving me a !Func# error that I can't control with "IsError".  Can someone please tell me what I am missing.

The [Description_5] field in like this 99% of the time:

8100-248-160-160-36

8177-234.1-160-80-24

etc.

A few are like this:

34 Short with No Chamfer

21 Long with Chamfer

MaterialCode: Format(IIf(Left([Description_5],1)="8",(Mid([Description_5],InStr([Description_5],"-")+1,(InStr(InStr([Description_5],"-")+1,[Description_5],".")-InStr([Description_5],"-"))-1)),999),"General Number")

Thanks for your help in advance.

Answer 1

My objective is to pull the two, three, or four digits after the first hyphen and before the period.

I'd wrap the logic in a little function:

Public Function GetMaterialCode(varDescription)

    Dim strDescription As String

    If Not IsNull(varDescription) Then

        If InStr(varDescription, "-") > 0 Then

            strDescription = Mid(varDescription, InStr(varDescription, "-") + 1)

            If InStr(varDescription, ".") > 0 Then

                strDescription = Left(strDescription, InStr(strDescription, ".") - 1)

            Else

                strDescription = Left(strDescription, InStr(strDescription, "-") - 1)

            End If

            GetMaterialCode = strDescription

        End If

    End If

End Function

You can see how it works in the debug window by entering your sample values:

? GetMaterialCode("8100-248-160-160-36")

248

? GetMaterialCode("8177-234.1-160-80-24")

234

? GetMaterialCode("34 Short with No Chamfer")

? GetMaterialCode("21 Long with Chamfer")

In the last two Null is returned.  You would still get an error, however, if a value contained only one hyphen, e.g.

? GetMaterialCode("42 Long with Half-Chamfer")

would return an 'invalid procedure call or argument' error on this line:

    strDescription = Left(strDescription, InStr(strDescription, "-") - 1)

but it would be a simple task to trap this in the function if necessary.

However, I would take serious note of what John says.  You are using the column as a data structure here.  Each column position in a row in a table should contain one value, and one value only of the attribute type modelled by the column.  To do otherwise violates a basic principle of the database relational model, Codd's Rule #2, The Guaranteed Access Rule:

Every datum (atomic value) in a relational database is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name.

Even though the data is imported you should really avoid the iceberg rather than moving the deckchairs on the Titanic, by parsing it into its constituent elements before inserting rows into a correctly designed table.  The non-conforming values like '34 Short with No Chamfer' would best go into a column of their own.  The parsing of the data could be automated by writing a set of suitable functions into each of which the original value is passed.

Answer 2

Hi,

if have a period is OK without a period obtain 998

MaterialCode: Format(IIf(Left([Description_5],1)="8",IIf(InStr([Description_5],".")=0,998,Mid([Description_5],InStr([Description_5],"-")+1,(InStr(InStr([Description_5],"-")+1,[Description_5],".")-InStr([Description_5],"-")-1))),999),"General Number")

Mimmo

Answer 3

Karl:

 See example data above and below.  Some of the records have periods in the data and some don't.  I know WHY I'm getting the function error.  I want to know how to control it.

John:

   This data is mined from another source, therefore it is all in one field.  I would put it in multiple fields IF I had designed it.  The only other option that I thought of would be generate the five (5) fields with a bunch of similar functions and populate those from the one string.  However, I still have to deal with the inconsistency of the linked data.

My objective is to pull the two, three, or four digits after the first hyphen and before the period.

Thanks Guys

75/75RH-SK (SC75/75FS2)
8025-25.21-016/016-12
8025-25.21-024/012-12
8025-25.21-024/024-12
8025-25.21-036/018-12

8030-15.92-252/014-34
8030-209.21-020/020-00
8030-209.21-028/028-00

Answer 4

You're paying the penalty for violating a basic rule of relational design: fields should be "atomic", storing only one fact. I have no idea what the different components of Description_5 are, but it appears that it should be at least five separate fields.

You may be able to write a custom VBA function using the Split() builtin function to tease out the different components of this field. 

Could you explain what result you want in the examples you post? What's the reason for looking for a period in the InStr?

Answer 5

It is looking for a period and not finding it.