Setting format for a field in a table with vba

[petern]

I got everything running smoothly now after my date problems in this post https://eileenslounge.com/viewtopic.php?f=29&t=34905. I was able to take the solution Hans gave me and automate it in such as way that my end user will only need to click a button to fix the date formatting. Everything works fine, except that the date field has @ in the format property when it's finished and that makes the date appear as a number. If I remove the @ manually then everything is fine. I'm using this code to change the field from a text field to a date field.

Code: Select all

Set db = CurrentDb()
db.Execute "ALTER TABLE Invoices " _
            & "ALTER COLUMN [Payment Date] DATE;"
db.Close
Set db = Nothing

I played around for a while with TableDefs and Properties to fix the format but couldn't get it to work. Any thoughts on how to automate that little bit would be welcome.

[HansV]

Try this:

Code: Select all

Dim db As DAO.Database
Dim tb As DAO.TableDef
Dim fl As DAO.Field
Set db = CurrentDb
db.Execute "ALTER TABLE Invoices " _
            & "ALTER COLUMN [Payment Date] DATE;"
Set tb = db.TableDefs("Invoices")
Set fl = tb.Fields("Payment Date")
fl.Properties("Format") = "Medium Date"

Let me know if this works.

[petern]

It worked perfectly. I was close but not nearly close enough. It's very tricky to find good information about this stuff online. Thanks so much for making it look easy.

Peter