Setting AllowZeroLength to True

[AlexisW]

I am currently working on a macro to do formatting on a table imported from excel by the user. (I was originally running a macro in excel)


The table needs to have a certain order to the columns, however I read that you can't change the position of an existing column. I found a macro which changes the field type by deleting and creating a new column and in the process, puts the column in a specific spot.

The only problem now is that the new column does not allow zerolength cells. I added and have tried multiple lines to allow the zero length but I continue to get the same error: Invalid Operation

Public Function ChangeFieldType(Fieldname As String, fieldpos As Integer, fieldtype As String)
Dim dbsData As Database
Dim tdf As TableDef
Dim fld As Field2
Set dbsData = CurrentDb
'---Create New Field
dbsData.TableDefs.Refresh
Set tdf = dbsData.TableDefs("TestTable")
Set fld = tdf.CreateField("MyFieldNew", fieldtype)
'Optional: set default value
fld.AllowZeroLength = True
fld.DefaultValue = "0"
'We set ordinal position, just after old field
fld.OrdinalPosition = fieldpos
'And append
tdf.Fields.Append fld
 
'Copy values from old field to a new one
dbsData.Execute _
"Update TestTable Set MyFieldNew=" & Fieldname, dbFailOnError
'Delete old field
tdf.Fields.Delete Fieldname
tdf.Fields.Refresh
'Rename new field to old
tdf.Fields("MyFieldNew").Name = Fieldname
tdf.Fields.Refresh
'Done!
Set tdf = Nothing
Set fld = Nothing
End Function

I have tried the following lines of code:

fld.AllowZeroLength = True
fld.AllowZeroLength.Value = True
fld.Properties("AllowZeroLength") = True
fld.Properties(AllowZeroLength) = True
fld.Properties("AllowZeroLength").Value = True
fld.Properties(AllowZeroLength).Value = True


Does anyone know the problem?
Or even does anyone know how to change the position of the column without this code?

Thanks for any help!



The original macro comes from: http://accessblog.net/2007/03/how-to...using-dao.html

[John_G]

If the change is to be permanent, just change the order of the fields in table design view.

But why is this an issue? You can easily rearrange the columns using a query, without having to modify the table design.

HTH

John

[AlexisW]

How can you rearragne the columns in a query?
I'm still fairly new to Access and therefore do not know all of the functions it is capable
the change is to be permanent so that the records (from an import, the excel table is not in the correct format) can be appended to the correct table where the rest of the records are stored

[John_G]

The are two ways to arrange columns in a query -

First is by adding the columns to the query (in design view) in the order you want to see them. If you want to move them around later, just drag them to where you want them, using the column headers.

If you use queries all the time to display the data in whatever order you need, the order of the fields in the table design doesn't matter. And, since you are importing from Excel, you probably don't want to change the table design anyway.

John

[AlexisW]

Thank you for helping me!
with your suggestions I have found a better way of doing what I want done