[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

IIf syntax issue

Posted on 2014-01-10
9
Medium Priority
?
276 Views
Last Modified: 2014-01-10
What is wrong with:

TotalHours1: IIf([NumberOfHours]="AE" Or "DC",0,[NumberOfHours])

--Steve
0
Comment
Question by:SteveL13
  • 4
  • 3
  • 2
9 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 39772362
You can't use the OR operator like that.

You can do this:

TotalHours1: IIf([NumberOfHours]="AE" Or [NumberOfHours]="DC",0,[NumberOfHours])
0
 

Author Comment

by:SteveL13
ID: 39772384
Didn't work.  Says expression typed incorrectly.
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 1000 total points
ID: 39772385
you can also use this


TotalHours1: IIf([NumberOfHours] IN ("AE", "DC") ,0,[NumberOfHours])


is the field [NumberOfHours] TEXT data type?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:SteveL13
ID: 39772394
Also didn't work.  Says expression typed incorrectly.
0
 

Author Comment

by:SteveL13
ID: 39772396
NumberOfHours is number type
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39772401
<NumberOfHours is number type >

then how come you are using the expression

IIf([NumberOfHours]="AE"

"AE"  is not NUMBER
0
 
LVL 85
ID: 39772405
Are you sure NumberOfHours is correct name?

Not sure (I don't use IIF very much) but you may need to nest them:

IIf([NumberOfHours]="DC",0,IIf([NumberOfHours]="AE",0,[NumberOfHours]))
0
 

Author Comment

by:SteveL13
ID: 39772408
I am very sorry to both of you/  I have the wrong field name between the brackets.  Is ok now either way.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39772409
you need to change this field [NumberOfHours] with the field that contains the values  "AE", "DC"
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question