Missing Bracket/Parenthesis in Expression on Form

[accessnewbie352]

Hello - I have been working on this one part of my database for the past week and might be going crazy but I tried entering an expression into a textbox:



=iif([Function]="Business",(sum([Total Curr])/sum([Total PY])-1),"")

and keep getting the error message that my expression is missing a closing parenthesis, bracket or vertical bar. I can't figure out what is missing in the formula though?????

[pbaldy]

Looks okay to me, and entering it into a textbox doesn't produce an error. Are you sure the error isn't coming from someplace else? Can you post the db here?

[accessnewbie352]

I exited out of my database and then re-tried the formula again and it works now...strange. not sure what was causing the error message. However, now I am getting blanks in the values because of my expression, which ends with =iif([Function]="Business",(sum([Total Curr])/sum([Total PY1])-1),""). When I go into the query that the form is based off of however, there is very much a value. Why is my expression not working?


Pic above shows the crosstab query - the columns across (Business, A, B, C, etc) are from the field "Function".

[ssanfu]

There is a difference in the two expressions:
Post #1:
=iif([Function]="Business",(sum([Total Curr])/sum([Total PY])-1),"")
Post #3:
=iif([Function]="Business",(sum([Total Curr])/sum([Total PY1])-1),"")

Sometimes Access does not provide, ummm, lets say 'accurate' error messages.

Not sure why you are getting the blanks.

BUT....... "Function" is a reserved word in ACCESS and shouldn't be used for object names.
Also, you shouldn't use spaces in object names. Either "TotalCurr" or "Total_Curr" (underscore) would be a better name.

[June7]

Why would you return an empty string to a number field? Return 0 or Null instead.

Are you sure SBU2 has "Business" records?

[accessnewbie352]

Why would you return an empty string to a number field? Return 0 or Null instead.

Are you sure SBU2 has "Business" records?

Sorry, maybe I wasn't clear - the spreadsheet view is the crosstab query I am trying to base my form off of and shows the correct values. I want to show blank where there are no values but even if SBU2 does not have a business record, I am trying to find the total for all Businesses (so sum of SBU1 - BUsiness, SBU2 - Business, SBU3 - Business, etc).

I don't want to return 0 because I don't want there to be a visible text in the form view when there is no actual value. Will try the Null but not sure if that will fix the issue?