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?

@Wyatt_Porter 

 

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.

@Wyatt_Porter 

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