[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1285
  • Last Modified:

Access 2013 IIF statement.

I am converting an Excel 2013 spreadsheet to an Access 2013 database.  I am trying to convert an if statement to an IIF statement in a calculated field.

My IIF statement
IIf([R1_CircOrder]="0"," ",(([R1_GrossCopies]-[R1_CircOrder])/[R1_CircOrder]))

when I try to save this I get an error that says.

"The expression could not be saved because it result type, such as binary or NULL, is not supported by the server.

Can someone tell me what I'm doing wrong.

Marshall
0
Mwvarner
Asked:
Mwvarner
  • 2
1 Solution
 
GozrehCommented:
Change it to
IIf(IsNull([R1_CircOrder]),0,(IIf(IsNull([R1_GrossCopies]),0,[R1_GrossCopies])-IIf(IsNull([R1_CircOrder]),0,[R1_CircOrder]))/IIf(IsNull([R1_CircOrder]),0,[R1_CircOrder]))

Open in new window


and the ResultType should be "Double"
0
 
Rey Obrero (Capricorn1)Commented:
try this

IIf([R1_CircOrder]=0,"",(([R1_GrossCopies]-[R1_CircOrder])/[R1_CircOrder]))

or
IIf([R1_CircOrder]=0,0,(([R1_GrossCopies]-[R1_CircOrder])/[R1_CircOrder]))

or
IIf([R1_CircOrder]=0,NULL,(([R1_GrossCopies]-[R1_CircOrder])/[R1_CircOrder]))
0
 
GozrehCommented:
The first one will not work, only the other 2.

but if R1_CircOrder or R1_GrossCopies will be null, the calculated result will be Null, so best solution will be always in calculated fields to use IIF(IsNull(Field),0,Field)
like this IIf(IsNull([R1_GrossCopies]),0,[R1_GrossCopies])
0
 
MwvarnerAuthor Commented:
The first one didn't work but the other two options did work for me.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now