Receiving #type! back on calculation

%3CLINGO-SUB%20id%3D%22lingo-sub-2959812%22%20slang%3D%22en-US%22%3EReceiving%20%23type!%20back%20on%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2959812%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20I%20am%20a%20little%20stuck%20and%20would%20like%20to%20know%20if%20anyone%20can%20help%20me%20with%20the%20below%20Expression%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIIf(%5BAsset_Type%5D%3D%221000%22%2C%5BBeginning%20Year%5D%2C%22%20%22)%2B15%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOk%2C%20so%20for%20the%20above%20Expression%20I%20am%20receiving%20back%20%22%23Type!%22%20for%20any%20%5BAsset_Type%5D%20not%20equal%20to%20%221000%22.%20I%20am%20trying%20to%20add%2015%20to%20the%20%5BBeginning%20Year%5D%20if%20%5BAsset_Type%5D%20%3D%201000%2C%20and%20%22%20%22%20%5BAsset_Type%5D%20is%20not%20%3D%20to%201000.%20Now%20I%20know%20that%20the%20%2B15%20at%20the%20end%20of%20the%20statement%20is%20making%20the%20Expression%20go%20crazy%2C%20but%20if%20I%20make%20try%20to%20use%20the%20expression%20below%20Access%20goes%20crazy.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIIf(%5BAsset_Type%5D%3D%221000%22%2C%5BBeginning%20Year%5D%2B15%2C%22%20%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20I%20am%20used%20to%20excel%2C%20and%20this%20above%20formula%20makes%20sense%20to%20me%2C%20but%20I%20can%20tell%20access%20takes%20a%20different%20approach.%20Would%20anyone%20be%20able%20to%20help%20me%20with%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2959812%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Eaccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2960401%22%20slang%3D%22en-US%22%3ERe%3A%20Receiving%20%23type!%20back%20on%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2960401%22%20slang%3D%22en-US%22%3ENo%20that%20won't%20work.%3CBR%20%2F%3ETo%20determine%20a%20better%20approach%2C%20is%20this%20expression%20in%20VBA%20code%20or%20a%20query%3F%3CBR%20%2F%3EAlso%2C%20if%20Asset%20Type%20is%20a%20number%20field%20then%20you'll%20need%20to%20remove%20the%20quotes%20around%201000%3C%2FLINGO-BODY%3E
Occasional Visitor

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