Set table format to standard

[RamziSaab]

basically when i import data from excel it does not put commas in between the numbers
e.g. 15000 should 15,000

is there a way to make it do it automatically or with a code for all number fields and tables. I know i have to set the format to standard but there are too many fields and tables to do it manually

any help will be greatly appreaciateed

 

[RamziSaab]

Found a code to do it...now is there a way to do so it selects all tables and all fields that are numbers??/


Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs("Your Table&quot

Set fld = tdf.Field("Your Field&quot

SetFieldProperty fld, "Format", dbText, "Standard"
SetFieldProperty fld, "DecimalPlaces", dbInteger, 0


The code for the SetFieldProperty procedure is:


Public Sub SetFieldProperty(ByVal fld As DAO.Field, ByVal strPropertyName As
String, ByVal iDataType As DAO.DataTypeEnum, ByVal vValue As Variant)
Dim prp As DAO.Property

Set prp = Nothing

On Error Resume Next
Set prp = fld.Properties(strPropertyName)
On Error GoTo 0

If prp Is Nothing Then
Set prp = fld.CreateProperty(strPropertyName, iDataType, vValue)
fld.Properties.Append prp
Else
prp.Value = vValue
End If
End Sub

 

[psemianonymous]

crossposted thread181-574548

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[RamziSaab]

Private Sub cbotest2_Click()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field


For Each tbl In CurrentDb.TableDefs
For Each fld In tbl.Fields
If fld.Type = dbstandard Then
SetFieldProperty fld, "DecimalPlaces", dbText, "0"


End If
Next fld
Next tbl
End Sub

foolio12 do u know what the decimal one does not work