Hi,

I have this formula in a query atm:

AMOUNT: IIf([PayMatch].[S1] Is Null,0,Val([PayMatch].[S1])/12)

Giving the value of 2736.117777777777
I want this to show 2736.12000000000

Ive tried incorperating the Round function but so far not getting it to work correctly. All help appreciated. this is in Access 97 so i dont think Round is working.

Tried using Value: Int(CDec([AMOUNT]+0.005)*100)/100 in an additional field but get "Undefined Function CDec* error

Many Thanks

AMOUNT: Round(IIf([PayMatch].[S1] Is Null,0,Val([PayMatch].[S1])/12),2) should give you 2736.12 (what do you get when you try it in A97?)

If you want all those trailing zeroes, then you can do:

AMOUNT: Format(Round(IIf([PayMatch].[S1] Is Null,0,Val([PayMatch].[S1])/12),2), "0.000000000")

AMOUNT: Round(IIf([PayMatch].[S1] Is Null,0,Val([PayMatch].[S1])/12),2) should give you 2736.12 (what do you get when you try it in A97?)

If you want all those trailing zeroes, then you can do:

AMOUNT: Format(Round(IIf([PayMatch].[S1] Is Null,0,Val([PayMatch].[S1])/12),2), "0.000000000")

When i try these in 97 it gives the error 'Undefined Function Round in expression' making me believe Round is only 2000+. I get the same error with CDec.

Oops - it turns out the round function isn't available in Access 97 - it was apparently introduced in A2K

There is a replacement function here you could try:
http://www.mvps.org/access/modules/mdl0054.htm

Oops - it turns out the round function isn't available in Access 97 - it was apparently introduced in A2K

There is a replacement function here you could try:
http://www.mvps.org/access/modules/mdl0054.htm

How would i integrate? still kinda new to Access

How would i integrate? still kinda new to Access

Create a new module in your access database (call it 'functions' or some such - it doesn't really matter).

Paste the code into the module and save/close it

Start using your new Round() function just as you would use any built-in function.

Create a new module in your access database (call it 'functions' or some such - it doesn't really matter).

Paste the code into the module and save/close it

Start using your new Round() function just as you would use any built-in function.

That works great so far, i can round it to 2 dec places but i still can't find a way for it then to display the following 0's after the 2 decimal places. Any idea how?


Rounded: Format(Round([AMOUNT5],2) "0.000000000")

Right click your control and set the # of decimal placed you want to see.