What is wrong with this formula?

[yamalady]

[Combined Dashboard Data]![CGrp] & [Combined DashboardData]![SCGrp] & "00000" & [Combined Dashboard Data]![ProgramType] & if(Len([Combined Dashboard Data]![Material)=10,"0",if(len( [Combined Dashboard Data]![Material])=9,"00","000")& [Combined Dashboard Data]![Ship From] & (if(Len([Combined DashboardData]![Ship From])=4,"-0000",[Combined Dashboard Data]![Ship From]))

Access is telling me I have an Invalid bracketing of name

[Combined Dashboard Data]![Material)=10,"0",if(len( [Combined Dashboard Data]![Material]

Maybe I've been staring at it too long to see the problem. Please help!

[ranman256]

IIF(
not if.

(but I didnt check any more on bracketing)

[ranman256]

also, if the formula gets too big like that , then make a custom function and use code to make it more readable.

[yamalady]

Oof...thanks....I've been excel too long. I changed that, but still didn't change the error.

[yamalady]

I am not that savvy. I don't know how to use code or to make a custom function.

[davegri]

[Combined Dashboard Data]![Material])=10,"0",if(len( [Combined Dashboard Data]![Material]

[yamalady]

Yes I believe I was staring at it too long!! That was it!!

[ranman256]

Public Function calcFormula(pvGrp, pvType, pvMat, pvShip)
Dim M, S
Select Case pvMat
    Case 10
      M = "0"
    Case 9
      M = "00"
    Case Else
      M = "000"
End Select

If pvShip = 4 Then
  S = "-0000"
Else
  S = pvShip
End If


calcFormula = pvGrp & "00000" & pvType & M & S
End Function