6 Replies Latest reply: May 15, 2014 9:58 AM by Jessica Daw

# Extracting an Amount

Hello,

On a report I am creating: in the table view, when a number in the 'coverage code' column is a set number, I am trying to take an amount out of the 'total' column and enter it into a new column.  I cannot seem to get the statement correct, and am not sure even if an If statement is correct to use for this.

When I try the following (and similar entries):

If(='21',[Total Charge Amount],"")

I receive an operand types error

Can you point me in the right direction?

Thank you,

Jessica Daw

• ###### Extracting an Amount

OK, you're close.

/size[list=1][]Is your calculated field numeric?  It should be as you are trying to return an amount which should be numeric.[/size][]In your calculated field you are returning a number if your statement is true, or a string ("") if it is false, this is your operand error.[/size][/list]

If(='21',[Total Charge Amount],0)[/b]

(this will return 0 (zero) if it doesn't match)

If you want it to actually be blank use:

If(='21',[Total Charge Amount],1/0)[/b]

1/0 will return a null value in Monarch...very helpful to use when you actually want a numeric number to be blank vice 0 (zero).

Hope this helps!

[size="1"][ June 24, 2003, 07:05 PM: Message edited by: Nick Osdale-Popa ][/size]

• ###### Extracting an Amount

Hi, Jessica!

Be sure and remove the single quote marks from around the number 21.  If it truly is numeric, the formula will treat it as a character if the marks are left there.

Nick's suggestions are usually flawless      , but this is one time he let his guard down and his computer got him with a good return kick      !

• ###### Extracting an Amount

Originally posted by Tom Whiteside:

Hi, Jessica!

Be sure and remove the single quote marks from around the number 21.  If it truly is numeric, the formula will treat it as a character if the marks are left there.

Nick's suggestions are usually flawless        , but this is one time he let his guard down and his computer got him with a good return kick        ! [/b][/quote]Hey! I resemble that remark!

Now, there is an assumption being done here as to whether is numeric or alpha-numeric. Yes, if it's numeric, the '' need to be removed, but the fact did remain that the formula was returning two different data types, a numeric value () if true or a blank string ("") if false.  This would be the most obvious cause for the operand types[/i] without knowing the how the fields are defined.

• ###### Extracting an Amount

Thank you, Nick and Tom!  The column numeric, so taking the ' is what needed to be done.  Also, the 1/0 to return a blank value worked when I exported to Excel, which is exactly what I needed, but when I exported to a text file the (null) appeared.  Is there anyway to actually return a blank value, or is (null) just the same in Monarch?

• ###### Extracting an Amount

Jessica, forgive me if you have already checked this, but on your main menu go to: Options - View - Display Null Values As: and see what is in the box.  The default value for versions 5 and 6 used to be (null).  If you clear this box, your nulls should display as blanks.

I usually export to MS Access files and Access hates blank values in the middle of numeric ones.  To overcome this, I use calculated fields to change the nulls to zero values, something like:

If(IsNull(), 0.00, )

(I learned early that setting Display Null Values As: 0.00, simply puts "0.00" in as text   :rolleyes:  .  Oh well!)

Hope this solves your display problem.  Good luck!

• ###### Extracting an Amount

It said (Null).  Thanks!  [img]smile.gif[/img]