Setting format for a field in a table with vba

petern
StarLounger
Posts: 72
Joined: 10 Feb 2010, 15:45
Location: Kitchener, Ontario

Setting format for a field in a table with vba

Post by 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.
Peter N

User avatar
HansV
Administrator
Posts: 68785
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Setting format for a field in a table with vba

Post by 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.
Regards,
Hans

petern
StarLounger
Posts: 72
Joined: 10 Feb 2010, 15:45
Location: Kitchener, Ontario

Re: Setting format for a field in a table with vba

Post by 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
Peter N