Invalid Syntax

[dullster]

I'm trying to build a calculated field in a table and i'm getting the error "The expression you entered contains invalid syntax".

This is my expression: IIf([Marital Status]="Married", [Basic Salary]<17100,0) IIf([Marital Status]="Married", [Basic Salary]>=17101,12) IIf([Marital Status]="Single", [Basic Salary]<6400,0) IIf([Marital Status]="Single", [Basic Salary]>=6401,12)

 

[tvanstiphout]

Probably best NOT to do that in an expression. I don't know what the values of 0 and 12 signify, but let's say they are "Points" given to such status.
UNDOUBTEDLY over time the salary cutoff values will change. It seems best to store this in a new table tblPoints:
BasicSalaryUpTo / MaritalStatus / Points
17100 / M / 0
99999 / M / 12
etc.
Then whenever you need to know the points, you can create a query that joins the employee table with tblPoints to retrieve the value.

 

[MajP]

The iif function is
IIf ( expr , truepart , falsepart )

IIf Function - Microsoft Support

In Access, if you need to calculate or display a value based on a condition, use the IIf function. The IIf (Immediate If) function is similar to the If function in Excel.

support.microsoft.com

Looks to me like your true part is not what you want returned when true, but another condition
[Marital Status]="Married" And [Basic Salary]<17100
then return 0 else return 12
[Marital Status]="single" And [Basic Salary]<6400
then return 0 else return 12

This can be done, but it is a pain to get correct and not as flexible as pointed out. If there are many more conditions and cut offs then use a small table.
This here kind of works, but may not meet all you other cases.

SELECT tblData.MaritalStatus, tblData.[basic salary],
IIf(([maritalStatus]="Married" And [basic salary]<171000) Or ([maritalstatus]="Single" And [basic salary]<6400),0,12) AS Expr1
FROM tblData;

MaritalStatus	basic salary	Expr1
married	$170,999.00	0
married	$171,101.00	12
Single	$6,399.00	0
single	$6,401.00	12

Query1

 

[plog]

IIf([Marital Status]="Married", [Basic Salary]<17100,0) IIf([Marital Status]="Married", [Basic Salary]>=17101,12) IIf([Marital Status]="Single", [Basic Salary]<6400,0) IIf([Marital Status]="Single", [Basic Salary]>=6401,12)

Everyone else has addressed the syntax. But you're logic isn't complete.

What if the [Marital Status] is neither 'married' nor 'single'?
What if the [Basic Salary] is >=6400 AND <=6401?

You haven't addressed those from a logic standpoint

 

[CJ_London]

you also can't run iif statements like that. The first statement (in bold) is complete - the rest will cause a compilation error

This is my expression: IIf([Marital Status]="Married", [Basic Salary]<17100,0) IIf([Marital Status]="Married", [Basic Salary]>=17101,12) IIf([Marital Status]="Single", [Basic Salary]<6400,0) IIf([Marital Status]="Single", [Basic Salary]>=6401,12)

 

[MajP]

@plog is addressing what I was getting at. This original is fine if there are no other cases. This one is probably more accurate.

SELECT
  tblData.MaritalStatus,
  tblData.[basic salary],
  IIf(([maritalStatus]="Married" And [basic salary]<171000) Or
          ([maritalstatus]="Single" And [basic salary]<6400),0,
             IIf(([maritalStatus]="Married" And [basic salary]>=171000) Or
                ([maritalstatus]="Single" And [basic salary]>=6400),12,Null)) AS Expr1
FROM tblData;

Then if there is another status you return null since you did not specify what to do. I think you want these cut

MaritalStatus	basic salary	Expr1
married	$171,000.00	12
married	$170,999.99	0
Single	$6,399.99	0
single	$6,400.00	12
Undetermined	$180,000.00
Single
Married

Query1