Why do you need to include "CAD - " in the same field? I can guess that it stands for Canadian Dollars or some such, but why include it in the field with the dollar amount? If you can put it in a separate field, you can use normal currency data formatting and this will be much simpler.
I've also experimented with this and have found that I can specify CAD $ as the currency symbol as data formatting in the inspector. Can't use enough characters to include the hyphen and spaces like you have though.
If you must use a calculation (and there are cases where this cannot be avoided), use a calculation such as This:
Let ( i = Int ( Total_price ) ;
"CAD - $" & i & "." & Left ( round ( ( Total_Price - i ) * 100 ; 0 ) & "00" ; 2 )
I have it this way strickly for asthetic purposes. The detail I left out and the reason I can't trick this by using the inspector is that CAD sometimes changes to USD but that wasn't the issue I was having it was with the total.
As always, thank you Phil!
I still wouldn't use the calculation for that case. I'd use a separate field for the CAD and USD info and not put it in this field in the first place.
but if I don't do it this way then I can't have the total perfectly centered as the number shrinks or grows. The total can be anywhere from 3 to 8 digits in length and I want to maintain a uniform looks throughout.
Right Justify the text field and left justify the currency field.
Use merge text for the two fields
Thank you, embarrassed to admit I'd never used a merge field before,always coded merges together. This is obviously far superior and efficient.
For some uses, but there are cases--such as when exporting data to meet the requirements of the receiving application, where you do need the calculation.
Also, when you have a large block of text with multiple merge fields, you can only specify one number format for all the number fields in the block of text. if one field is a simple number that should be formatted as entered and another is currency, you end up using the calculation field as your are otherwise forced to specify currency format or "as entered" for both fields.