Forum Discussion

Wyatt_Porter's avatar
Wyatt_Porter
Copper Contributor
Nov 12, 2021

Receiving #type! back on calculation

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?

access
  • isladogs's avatar
    isladogs
    MVP
    Nov 12, 2021

    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?

  • tims's avatar
    tims
    Copper Contributor
    Dec 02, 2021

    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.

  • Gustav_Brock's avatar
    Gustav_Brock
    Brass Contributor
    Dec 03, 2021

    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

Resources