Removing occasional last letter

[dakpluto]

I have been using the following expression in one of my databases for a while now:



New Rate Price: Right([Fortlauderdale_target.Rate_Code],4)/100

This is to pull the rate out of a code that looks like the following:

O0135
O0138
S0363
etc.

However, with some changes in the company, I now occasionally get the following:
S0418D
O0135D
S0616D

it's random on when that trailing D will appear. So how do I adjust my original expression to remove the D when it appears?

[JoeM]

You can use the RIGHT function to check the right-most characters to see if it is a number (use the ISNUMERIC function, see: http://www.techonthenet.com/access/f.../isnumeric.php).

That should give you the missing piece to do what you need. If you run into trouble putting it all together, post back to this thread.

[dakpluto]

Gonna need some help on how to include it, but does sound like the right idea. I'm sure it's gonna incorporate some if statements, maybe some nested if statements, but not sure how to throw it all together.

[JoeM]

New Rate Price: IIf(IsNumeric(Right([Rate_Code],1)),Right([Rate_Code],4),Left(Right([Rate_Code],5),4))/100

[dakpluto]

And after all of that....it dawned upon me there is a way 500 thousand times easier....

New Rate Price: Mid([Fortlauderdale_target_20130429.Rate_Code],2,4)/100

/facepalm

Dunno why I didn't think of it earlier.

[dakpluto]

New Rate Price: IIf(IsNumeric(Right([Rate_Code],1)),Right([Rate_Code],4),Left(Right([Rate_Code],5),4))/100

I tested this also in case I ever need it for the future, works perfectly also. Thanks Joe (o.o)b

[JoeM]

Yes, that would be easier. I didn't want to assume that you always had just a one-letter prefix. If you do, that simplified one will work. If it could be more than one, then the one I posted will work.