By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,287 Members | 1,665 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,287 IT Pros & Developers. It's quick & easy.

The Expression you enters contains invalid syntax (if statement)

P: 2
kk so ive been looking at it (and im pretty new to access)
but i dont know what is wrong with my statement
plz help

Expand|Select|Wrap|Line Numbers
  1. = SiteIncome IIf([LocationsT].[SiteID]=1,
  2.    [Totalincome].[castleblackincome],
  3.       IIf([LocationsT].[SiteID]=2,
  4.             [Totalincome].[Torontoincome],
  5.                IIf([LocationsT].[SiteID]=3,
  6.                   [Totalincome].[Edmontonincome],
  7.                      "")))
Apr 22 '14 #1
Share this Question
Share on Google+
3 Replies

P: 2
im retarded
sorry for the waste of anyones time
i just say the = is in the wrong place
Apr 22 '14 #2

Expert Mod 5K+
P: 5,397
No big deal
Typos haunt all of us (^_^)

In the future, please use the code button to format your posted scripts or formated text.
Apr 22 '14 #3

Expert Mod 5K+
P: 5,397
You don't mention where you are using this construct.
I find nested IIF() to be a pain to follow.
So instead you may want to consider the switch() function

In a calculated query field you can do something like this:
Expand|Select|Wrap|Line Numbers
  1. zs: Nz(
  2.    Switch([people_pk]=6,"six",
  3.           [people_pk]=7,"seven",
  4.           [people_pk]=8,"eight"),
  5.    "No Match")
Here I use the NZ() to act like a case-else

The same will hold true in a form or report text field, simply replace the "zs:" with an equal-sign.

So in your case it would appear to be (in the query):

Expand|Select|Wrap|Line Numbers
  1. CalcIncome: Nz(
  2.    Switch([LocationsT].[SiteID]=1,[Totalincome].[castleblackincome],
  3.           [LocationsT].[SiteID]=2,[Totalincome].[Torontoincome],
  4.           [LocationsT].[SiteID]=3,[Totalincome].[Edmontonincome]),
  5.    0)
replaceing "CalcIncome:" with "=" for use in a form/report control.
In this case if your conditions fail, thus returning a null value, I've trapped this, so-to-speak, to return a zero value.
Apr 22 '14 #4

Post your reply

Sign in to post your reply or Sign up for a free account.