To Val() or not to Val() 1

[rayna]

Hello everyone and Happy Holidays!

I have a query I am writing where I am getting data from another database and the data is in the form of a string - 0.89% and 0.74% (represented below by aa1 and aa2. I need to use that percentage along with another in a calculation and tried this:

TY/LY2: IIf(Val(Mid([aa2],1,InStr([aa2],"%&quot)),Format(Val(Mid([aa1],1,InStr([aa1],"%&quot))/Val(Mid([aa2],1,InStr([aa2],"%&quot)),"Percent&quot,"0.00%&quot

However, for some reason, VAL() insists on rounding UP, and so instead of 0.89/0.74, I get 1/1 and get a consistent 100%, which of course is totally inaccurate. I have tried all the 'C' functions as well (CDbl etc...) and they give me no better results.

Does anyone have an idea as to how I can accomplish this so I get a true percentage?

Thanks so much!
Rayna Man plans. God laughs.

 

[FancyPrairie]

How 'bout using CDbl instead of Val.

TY/LY2: IIf(CDbl(Mid([aa2],1,InStr([aa2],"%&quot)),Format(CDbl(Mid([aa1],1,InStr([aa1],"%&quot))/CDbl(Mid([aa2],1,InStr([aa2],"%&quot)),"Percent&quot,"0.00%&quot

 

[rleverock]

In some instances Access will convert strings automatically in calculations. Try this:

TY/LY2: Format(Mid([aa1],1,InStr([aa1],"%&quot-1)/Mid([aa2],1,InStr([aa2],"%&quot-1),"Percent&quot

You need the -1 after the instr() function so the ending point for the mid() function doesn't include the "%" symbol.

Hope this helps

Ron

 

[rayna]

Ron,

Oh my Gosh!!! Thank you, thank you, THANK YOU!!! You have no idea how long I have been messing with this trying to make it work!!!!!

That "-1" thing I have never seen before and had no idea it was necessary.

Again, I can't tell you how much I appreciate this.

Rayna


Man plans. God laughs.