Hi all. Here's my problem:



I have an access db with 250000 records. some of my fields (number/double 

datatype) have values with more than 8 digits after the first decimal 

(comma). I must update my database so it contains values with only 2 

digits after the decimal. I tried an update with asp page but it took too 

long and the script stoped executing and updated only a small number of 

records. I also changed the datatype to "2 decimals" but it didn't affect 

the historical data.



More than that, i want not only to "cut" all the digits after the second, 

but to approximate the value to the closest ,10. Can anybody help me how 

to do this with Access???

> Hi all. Here's my problem:

> 

> I have an access db with 250000 records. some of my fields 

(number/double datatype) have values with more than 8 digits after the 

first decimal (comma). I must update my database so it contains values 

with only 2 digits after the decimal. I tried an update with asp page but 

it took too long and the script stoped executing and updated only a small 

number of  records. I also changed the datatype to "2 decimals" but it 

didn't affect the historical data.    More than that, i want not only 

to "cut" all the digits after the second, but to approxthe value to the 

closest 10. Can anybody help me how 



Access97 doesn't have a round function. Here's one I wrote, you need to go 

through the table and replace every number with the rounded number. I 

would make another copy of the table first (copy it, paste it, call it 

mytable_save) to be safe. Here's the code. Carol :-)

'-----------------------------------------------------------------

Public Function Round(inAmt As Single) As Single



Dim intX As Integer, i As Integer

' this creates a 4 decimal place number from a percentage >4 decimal

'places, then rounds it to 3 places



'ex:   cint(.00204081632 * 1,000,000)/10 = 204.0

inAmt = CInt(1000000 * inAmt) / 10



'do this twice:  ends up 20.4, 2.0

For i = 1 To 2

    If (inAmt - Fix(inAmt)) >= 0.5 Then

        inAmt = (Fix(inAmt) + 1) / 10 'round up

    Else

        inAmt = Fix(inAmt) / 10 'truncate (round down)

    End If

Next i



Round = inAmt / 1000



End Function

'---------------------------------------------------------



Call this function like this:



private proc RoundAll()



dim db as database

dim tbl as recordset



set db = currentdb()

set tbl = db.OpenRecordset("myTable",dbOpenDynaset)



do while not tbl.eof

    tbl.edit

    tbl.bignumber = Round(tbl.bignumber)

    tbl.update

loop



end proc

Hi





More than that, i want not only to "cut" all the digits after the second, but to approximate the value to the closest ,10. Can
anybody help me how 

to do this with Access???



-- try the round function

syntax round(<numbertoround>,<numberofplacestoround>)

e.g. - round(1234.56789,2) = 1234.57



Regards 

Shreekar Joshi

Hi Carol.....Thanks for your answer but i suppose this is about Visual 

Basic isn't it?? Unfostunatelly, i'm not aware of VB and what tools could 

i use to write the function and call it. Can you give me more specific 

instructions??



> > Hi all. Here's my problem:

> > 

> > I have an access db with 250000 records. some of my fields 

> (number/double datatype) have values with more than 8 digits after the 

> first decimal (comma). I must update my database so it contains values 

> with only 2 digits after the decimal. I tried an update with asp page 

but 

> it took too long and the script stoped executing and updated only a 

small 

> number of  records. I also changed the datatype to "2 decimals" but it 

> didn't affect the historical data.    More than that, i want not only 

> to "cut" all the digits after the second, but to approxthe value to the 

> closest 10. Can anybody help me how 

> 

> Access97 doesn't have a round function. Here's one I wrote, you need to 

go 

> through the table and replace every number with the rounded number. I 

> would make another copy of the table first (copy it, paste it, call it 

> mytable_save) to be safe. Here's the code. Carol :-)

> '-----------------------------------------------------------------

> Public Function Round(inAmt As Single) As Single

> 

> Dim intX As Integer, i As Integer

> ' this creates a 4 decimal place number from a percentage >4 decimal

> 'places, then rounds it to 3 places

> 

> 'ex:   cint(.00204081632 * 1,000,000)/10 = 204.0

> inAmt = CInt(1000000 * inAmt) / 10

> 

> 'do this twice:  ends up 20.4, 2.0

> For i = 1 To 2

>     If (inAmt - Fix(inAmt)) >= 0.5 Then

>         inAmt = (Fix(inAmt) + 1) / 10 'round up

>     Else

>         inAmt = Fix(inAmt) / 10 'truncate (round down)

>     End If

> Next i

> 

> Round = inAmt / 1000

> 

> End Function

> '---------------------------------------------------------

> 

> Call this function like this:

> 

> private proc RoundAll()

> 

> dim db as database

> dim tbl as recordset

> 

> set db = currentdb()

> set tbl = db.OpenRecordset("myTable",dbOpenDynaset)

> 

> do while not tbl.eof

>     tbl.edit

>     tbl.bignumber = Round(tbl.bignumber)

>     tbl.update

> loop

> 

> end proc

> 

>