coded field data sub-type conversion.

[jackal63]

I have a make table query in my database that makes a table called "footage". When that table is created it contains a field called "docket", which is a four digit code number. Access makes this "docket" field a number with a field size of "double". In order to run a query on this made table, that matches against the "docket" field, I have to go into design mode for the table "footage" and change the field size property to "decimal" with a precision of "4". Obviously I cannot distribute this report to users until it is a one button process.

Does anybody know some code I could squeeze between the two queriers, to automatically change the field size of field "docket" to decimal with a precision of "4" ??

Or if I could ensure that "docket" would be given a field size of "decimal" with precision of "4", while the table was being created by my first query, that would be really cool too.

Thanks in advance

 

[BattleDroid42]

Use DAO to create your table and append your records instead:

Private Sub CreateLists_Click()
On Error GoTo CreateListsError

Dim dbs As Database
Dim qdf As QueryDef
Dim tdf As TableDef
Dim fld As Field
Dim SQL As String

Set dbs = CurrentDb()

Set tdf = dbs.CreateTableDef("Table1&quot
Set fld = tdf.CreateField("ID", dbLong)
fld.Attributes = fld.Attributes + dbAutoIncrField
tdf.Fields.Append fld
Set fld = tdf.CreateField("ItemNumb", dbLong)
tdf.Fields.Append fld
Set fld = tdf.CreateField("Source1", dbText, 10)
tdf.Fields.Append fld
Set fld = tdf.CreateField("Source2", dbText, 10)
tdf.Fields.Append fld
Set fld = tdf.CreateField("Decimals", dbDouble)
tdf.Fields.Append fld
dbs.TableDefs.Append tdf

etc...