I want to create a table using access vba with formatted fields. How do I format currency field with "standard" format while creating the table?
Private Sub CreateTable_Click()
DoCmd.RunSQL "CREATE TABLE Test( " & _
"[id] AUTOINCREMENT PRIMARY KEY, " & _
"[transaction_date] DATE, " & _
"[reference] TEXT(255)," & _
"[details] TEXT(255)," & _
"[debit] CURRENCY," & _
"[credit] INT);"
End Sub
We use DAO for this.
Here's an example. At the bottom, a Format is assigned to a field:
' Creates the local tables and indexes from scratch if missing.
' Returns True if success, False if not.
'
' 2017-11-14. Gustav Brock. Cactus Data ApS, CPH.
'
Public Function CreateLocalDataTable( _
ByVal TableName As String) _
As Boolean
Dim Database As DAO.Database
Dim Table As DAO.TableDef
Dim Field As DAO.Field
Dim Index As DAO.Index
Dim Property As DAO.Property
Dim Result As Boolean
Set Database = CurrentDb
For Each Table In Database.TableDefs
If Table.Name = TableName Then
' Table exists. Exit.
Result = True
Exit For
End If
Next
If Table Is Nothing Then
' Create table.
Select Case TableName
Case "Country"
Set Table = Database.CreateTableDef(StrConv(TableName, vbProperCase))
Set Field = Table.CreateField("Code", dbText, 2)
Field.AllowZeroLength = False
Field.Required = True
Table.Fields.Append Field
Set Field = Table.CreateField("Name", dbText, 45)
Field.AllowZeroLength = False
Field.Required = True
Table.Fields.Append Field
Set Index = Table.CreateIndex("PrimaryKey")
Set Field = Index.CreateField("Code")
Index.Fields.Append Field
Index.Primary = True
Table.Indexes.Append Index
Case "Zone"
Set Table = Database.CreateTableDef(StrConv(TableName, vbProperCase))
Set Field = Table.CreateField("ZoneId", dbInteger)
Field.Required = True
Table.Fields.Append Field
' Don't create an index on CountryCode as this will
' be created when creating referential integrity.
Set Field = Table.CreateField("Name", dbText, 35)
Field.AllowZeroLength = False
Field.Required = True
Table.Fields.Append Field
Set Index = Table.CreateIndex("PrimaryKey")
Set Field = Index.CreateField("ZoneId")
Index.Fields.Append Field
Index.Primary = True
Table.Indexes.Append Index
Set Index = Table.CreateIndex("CountryCode")
Set Field = Index.CreateField("CountryCode")
Index.Fields.Append Field
Table.Indexes.Append Index
Set Index = Table.CreateIndex("Name")
Set Field = Index.CreateField("Name")
Index.Fields.Append Field
Table.Indexes.Append Index
Case "Timezone"
Set Table = Database.CreateTableDef(StrConv(TableName, vbProperCase))
Set Field = Table.CreateField("ZoneId", dbInteger)
Field.Required = True
Table.Fields.Append Field
Set Field = Table.CreateField("Abbreviation", dbText, 6)
Field.AllowZeroLength = False
Field.Required = True
Table.Fields.Append Field
Set Field = Table.CreateField("TimeStart", dbCurrency)
Field.Required = True
Table.Fields.Append Field
Set Field = Table.CreateField("Offset", dbLong)
Field.Required = True
Field.DefaultValue = "0"
Table.Fields.Append Field
Set Field = Table.CreateField("Dst", dbBoolean)
Field.DefaultValue = "False"
Table.Fields.Append Field
' Don't create an index on ZoneId as this will
' be created when creating referential integrity.
Set Index = Table.CreateIndex("Abbreviation")
Set Field = Index.CreateField("Abbreviation")
Index.Fields.Append Field
Table.Indexes.Append Index
Set Index = Table.CreateIndex("TimeStart")
Set Field = Index.CreateField("TimeStart")
Index.Fields.Append Field
Table.Indexes.Append Index
End Select
If Not Table Is Nothing Then
' Append table.
Database.TableDefs.Append Table
' Append properties.
Select Case TableName
Case "Timezone"
Set Field = Table.Fields("TimeStart")
Set Property = Field.CreateProperty("Format", dbText)
Property.Value = "0"
Field.Properties.Append Property
End Select
Result = (Err.Number = ErrorNone)
End If
End If
CreateLocalDataTable = Result
End Function
CURRENCYtype, you might consider usingMONEY.