Concatenation with IIF statement

[mattstrachan]

I am attempting to create a printed label with a concatenated field. I want the field to abbreviate several shapes and colors but i will just start with the shape.

=IIf([Shape]="round","RD","TH")

This is what my basic statement says. I am attempting to nest other options inside of this.

=IIf([Shape]="round","RD",IIF([Shape]="oval", "OV", IIF([Shape]="triangle", "TR", "TH")))

Is this the correct syntax? I can not seem to get it to work. Any suggestions?

 

[pr2-eugin]

Hello mattstrachan, Would it not be simple if you just create a custom function? For three this might seem to be simple (not in the above case though ), but as the list increases so will the complexity of the IIF.. It would be something along the lines of..

Public Function abbrevShape(shapeName As String) As String
    Select Case shapeName
        Case "Round"
            abbrevShape = "RD"
        Case "Oval"
            abbrevShape = "OV"
        Case "Triangle"
            abbrevShape = "TR"
        Case Else
            abbrevShape = "TH"
    End Select
End Function

Or also create a table with the code and it corresponding abbreviation and finally just use DLookUp..

 

[mattstrachan]

That would work perfect. I am unsure of how to use functions in access though. I have been writing simple "Print Report" button controls, but where would I start to use the code you supplied?

 

[pr2-eugin]

Copy the above code into a module.. This way it can be called from anywhere in the project, then on the report, in the unbound control instead of having =IIF, use..

= abbrevShape(theControlThatHasTheShapeName)

 

[mattstrachan]

Ok, so here is my function.

Public Function abbrevShape(Shape As String) As String
Select Case Shape
Case "baguette"
abbrevShape = "BG"
Case "cushion"
abbrevShape = "CC"
Case "emerald"
abbrevShape = "EM"
Case "heart"
abbrevShape = "HS"
Case "marquise"
abbrevShape = "MQ"
Case "octagon"
abbrevShape = "OC"
Case "oval"
abbrevShape = "OV"
Case "princess"
abbrevShape = "PC"
Case "pear"
abbrevShape = "PS"
Case "radiant"
abbrevShape = "RC"
Case "round"
abbrevShape = "RD"
Case "trillion"
abbrevShape = "TR"
Case Else
abbrevShape = "TH"
End Select
End Function


Now I would like to call this function in a form to display the abbrevShape. I have tried to use =abbrevShape(Shape) but I am receiving an #Name? error. Am I missing a step?

 

[Brianwarnock]

Just to be clear as you are not used to functions, the function has to be placed in a module that does NOT have the same name as the function.

Brian

 

[mattstrachan]

Ok, so I now have the function working correctly for previously entered data. I have one more quick question to add on to this:

How do I use this function abbrevShape when I am entering new data? I simply get a #Type? error.

Is there something like:

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

That I could use?

 

[jdraw]

I would recommend you build a table of shapes
tblShapes

shapeCode
shapeName

Then create a query between whatever table you currently have with these shapes and this tblShapes.

But I'm not sure I really know what you goal is.