# How remove decimal and 2 trailing zeros from multiple values in field

Hello,

I have a table that has numeric values (eg. 2506.00) that I have already convert to text.  Now I need to remove the decimal and the 2 trailing zeros.  The field name is Group_Index.  Also, in case you need to know, the values are not all 4 digits. They actually start at 1.00, then go all the way to 2506.00.

So I want 2506.00 to be changed to 2506.

Would I do this using an update query?  How should it be written?  If you can provide me with the SQL that would be even better!

Thanks!
###### Who is Participating?

Commented:

UPDATE Parcel_Info SET Parcel_Info.Group_Index = Left(Group_Index,Len(Group_Index)-3   );
0

Commented:
u could try round

round(2506.00,0)
0

Commented:
or format

format(2506.00,"0")
0

Commented:
update mytable set myfld = replace(myfld,".00","");
0

Commented:
yes, update query, thats what u asked for in your last sentence!
Well I guess GRayL has given u the syntax
and after re-reading, values are not all digits

stick with his solution

0

Author Commented:
Couldn't get it to work.

Here is the SQL:

UPDATE Parcel_Info SET Parcel_Info.Group_Index = replace(Group_Index,".00","");

Here is the error message: Undefined function 'replace' in expression

Let me know what I'm doing wrong, please.

BTW, using Access 97 is that is relevant.

Phil.
0

Commented:
If its text you could use instr() to locate the decimal point then take everything to the left of it.

heres the syntax

Expr1: IIf(InStr([field1],".")=0,[field1],Left([field1],InStr([field1],".")))

Just an idea.

Carl.
0

Author Commented:
Carl,

Almost worked.  Took the 2 trailing zeros off.  But left the decimal.

So it went from this: 2506.00

to this 2506.

Any ideas?
0

Commented:
Replace does not work with Jet In A97.  You could also try:

UPDATE Parcel_Inof SET Parcel_info.Group_Index = Left(Group_Index,Instr(Group_Index,".00")-1)
0

Commented:
Ray, here is a version of Replace for A97

Public Function A97Replace(pstrIn, pstrOld, pstrNew)

Dim strWork As String
Dim strL As String
Dim strR As String
Dim intX As Integer
strWork = pstrIn

Do
intX = Nz((InStr(1, strWork, pstrOld, vbTextCompare)), 0)
If intX > 0 Then
strL = Left\$(strWork, intX - 1)
strR = Right\$(strWork, (Len(strWork) - intX - Len(pstrOld) + 1))
strWork = strL & pstrNew & strR
Else
Exit Do 'Escape from the loop if nothing else to do
End If
Loop

A97Replace = strWork
End Function

so u could still use your original solution

update mytable set myfld = a97replace(myfld,".00","");

UPDATE Parcel_Info SET Parcel_Info.Group_Index = A97Replace(Group_Index,".00","");

But Jim's and your alternative solution will work just as well so as long as it just ends with .00

0

Commented:
By the way, the post if for information only, not an attempt to get points! so please do not accept my post as part of any solution
0

Commented:
No objecions
0

Author Commented:
Hello All,

I apologize for not responding sooner, but I just got back from a two week vacation, yesterday.

BTW, you all have been a TREMENDOUS help to me in getting a better grip on how to use Access  and I GREATLY apprieciate having a forum like this!

Have a great day!

Phil
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.