Remove decimal from text field within MS Access table 2

[IngDep]

Have two tables within MS Access database - tblCaseData
and tblDetail that have a Plan code field.

tblCaseData has decimals within the Plan code field such as 286.35 whereas tblDetail has 28635.

What is the quickest method to eliminate the decimal points within the Plan code field within tblCaseData?

Currently exploring the use of the format function.

Note, tblCaseData has over 900,000 records.

Ultimately, I am interested in joining on the Plan code field for the abovementioned tables and "pulling" the Plan code description from tblDetail and populating a new table that contains all of the records from tblCaseData and the matching fields from tblDetail.

Thanks in advance.

 

[Golom]

I assume that these fields are text fields and not numeric. If so

UPDATE tblCaseData

SET [Plan Code] = Replace([Plan Code], ".", "")

Where Instr([Plan Code], ".") > 0

Might take a while to run with 900,000 records but it should be a one-time thing.

 

[IngDep]

Error received upon running the following is "External name not found."



Sub UpdateTable()
Update 00002_tblCaseData

Set [Plan_Code] = Replace([Plan_Code], ".", "")

Where InStr([Plan_Code], ".") > 0

End Sub

 

[Remou]

Try:

Sub UpdateTable()
strSQL="Update 00002_tblCaseData " _
& "Set [Plan_Code] = Replace([Plan_Code], '.', '') " _
& "Where InStr([Plan_Code], '.') > 0 "
CurrentDB.Execute strSQL
End Sub

 

[IngDep]

Remou,

Worked great!

Thanks.