Microsoft Reporting Tool Expression

Posted on 2006-05-29
Last Modified: 2010-04-23
Hi there,

I'm new to Microsoft Reporting tool and I'm trying to create an expression which looks at a field on the report and returns a value of 0 if the field value is "CASH" and a 1 if the field value is "CARD".

It seems pretty straightforward but the formula never evaluates to 0, it always defaults to 1 even when the paymenttype field is CASH.

Is there a glitch in this program that anyone knows of where I have to do some kind of Cast or something?

My formula is:

Thank you for your help.
Question by:boukaka

    Author Comment

    I need to change my question!! I just found out that the field paymenttype.Value has 8 trailing spaces after the word CASH, my IIF formula now works.

    HOWEVER, in order to avoid this problem in the future, is there a way to convert fields in these reports as you include them to remove extra spaces so that when I'm creating formulas I don't have to watch for hidden spaces?

    LVL 62

    Accepted Solution

    Hi boukak;

    I have not used Microsoft Reporting tool but when you need to remove spaces at the beginning and end of a string when assigning to a variable or retrieving it from a variable you can use the Trim function of String.

    So in your statement
    you can do this to remove leading and trailing spaces.
        =IIF(Fields!paymenttype.Value.Trim ="CASH",0,1)


    Author Comment

    LVL 62

    Expert Comment

    by:Fernando Soto
    No problem. Glad I was able to help. :=)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
    Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now