Question Fixed Formula in Access or Continuous Transfer to Excel

[JewliaB]

I am somewhat experienced in Excel, but not as much in Access. I understand that they are very different, but in Excel there is a formula called fixed which can take a calculated sum into another cell and translate it into a text format with only two decimal places. I use this formula for a mail merge I have where I include an "invoice" at the bottom of the page and need to calculate tax, but don't want all fifteen decimal places, as that is what is stored if not shown in the calculated cell, to be translated into word properly. Is there a way to use this type of formula in Access? If not, can someone tell me how to create a continuous feed into Excel from Access so that when I update the Access file it will update the Excel file where I can use a fixed formula and thus can pull the mail merge from Excel.

Thanks, Jewlia

 

[KenHigg]

You can use Excel functions in MS Access by setting your references up. In an Access vba window do Tools->References then scroll down and select 'MS Excel 11.0 Object Library'. Yours may be a little different, but thats the basics.

 

[Rabbie]

Try looking at the Round function in Access help. You can specify the number of decimal digits

 

[neileg]

CStr(Round([MyValue],2)) will convert a decimal number to a text value with 2 decimal places

 

[gemma-the-husky]

if you have a number say

12.345 and you choose to display two dps, then you are still storing 12.345, but displaying

12.34, or 12.35 depending on rounding settings.

--------
now if you want to actually store 12.34 you have to do some math

you have to multiply by 100, then round it to an integer, then divide by 100 again

so effectively.

fixednumber = clng(originalnumber*100)/100

-------
you may need to look at the function a bit more carefully if this doesnt round exactly as you want it to.