Convert ToNumber

Hello,

In my prompt option, when I type "10" for 2010, it is not returning the 2010 values. The formula that references the ?Year is below:  

ToNumber(Right(ToText(Year({ap_invoice_detail.trans_date})),4)) = ToNumber({?Year})

It has worked great until 2009 but for 2010 it doesn't look like it is converting it to string correctly. The formula seems to be ok but I am not sure why it is not returning 2010 records in the output?
sgaurav7Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
You are comparing the numeric value  of the last 4 characters from a totext result with a ToNumber result.

The last 4 charcters from the totext() will have the pattern
9.99

For 2009 you would get 9.00 and for 2010 you would get 0.00.
So when you convert this to a number you get 9 for 2009, which matches the parameter; but for 2010 you get  0, which you are comparing with 10.

If you are only dealing years after 2000 then do..

Year({ap_invoice_detail.trans_date}) = ToNumber({?Year})+2000



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike McCrackenSenior ConsultantCommented:
Try this

ToNumber(Right(ToText(Year({ap_invoice_detail.trans_date}),0,""),4)) = ToNumber({?Year})

The ToText converts the number to 2,010.00.  I changed it so it converts it to 2010

ToText({NumberField},DecimalPlaces,ThousandsSeparator)

mlmcc

sgaurav7Author Commented:
Hello,

I tried both formulas and they both worked. Thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.