Receiving #type! back on calculation

Copper Contributor

So I am a little stuck and would like to know if anyone can help me with the below Expression:


IIf([Asset_Type]="1000",[Beginning Year]," ")+15


Ok, so for the above Expression I am receiving back "#Type!" for any [Asset_Type] not equal to "1000". I am trying to add 15 to the [Beginning Year] if [Asset_Type] = 1000, and " " [Asset_Type] is not = to 1000. Now I know that the +15 at the end of the statement is making the Expression go crazy, but if I make try to use the expression below Access goes crazy. 


IIf([Asset_Type]="1000",[Beginning Year]+15," ")


Now I am used to excel, and this above formula makes sense to me, but I can tell access takes a different approach. Would anyone be able to help me with this?

3 Replies

No that won't work for several reasons.

#Type errors indicate an expression is incorrect for one or more datatypes used
If Asset Type is a number field then you'll need to remove the quotes around 1000

Also I assume Beginning Year is a number field so it can't be set to an empty string


To determine a better approach, is this expression in VBA code or a query?



IIf([Asset_Type]="1000",[Beginning Year]," ")+15


-That fails for a valid reason
On records <> 1000, you're attempting to assign a value of empty space and then add 15 to it.


IIf([Asset_Type]="1000",[Beginning Year]+15," ")

- This probably fails for a reason we can't see


Because we don't know the data type of [Beginning Year], to which you're attempting to add 15.  So if [Beginning Year] is empty, null or text from your data source, then you'll get an error attempting to add 15 to it.


I'd experiment along the lines of what @Isladogs mentioned and verify your data types.


One solution, maybe try wrapping a Clng function around [Beginning Year] to force a long data type within the expression before you perform the arithmetic operation.


Such as;

IIf([Asset_Type]="1000",Clng([Beginning Year])+15," ")


If [Beginning Year] is already a numeric, then you would have to assign 0 or some other numeric, instead of " " as the value when <>1000.


You can use:

IIf([Asset_Type]="1000",[Beginning Year],0)+15


If Beginning Year can be Null:

IIf([Asset_Type]="1000",Nz([Beginning Year],0),0)+15