[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1310
  • 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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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