VBA - Change Access date format to custom

[RuiFlora]

Hello,

I am currently using a macro to copy data from Excel to an Access database. There are some fields that i need to have in access as "mmmm yyyy" (Ex: December 2016) but access seems to be ignoring my attempts to change the data format. My code is the following for the VBA query:

(...)

qSQL = "UPDATE Flatfile SET [Start Date] = " & "Format([Start Date]," & " ""mmmm yyyy"")"
cn.Execute qSQL

(...)

I don't know what i am doing wrong, i am setting the column [Start Date] to be in the specified format, but it keeps dd/mm/yyyy.

Does someone know the correct SQL query?
Thanks

 

[Johnny C]

Have you got [Start date] set to date format in the Access Db? If so it doesn't matter what format you send from Excel, Access will convert it to dd/mm/yyyy, you'd need to change the Access side to fix it. Go into table design view and put mmmm yyyy in the format field, that fixes it.

If that does what you want you don't need the format element in the VBA, just send it the date and access will load it fine and show it as mmmm yyyy

 

[RuiFlora]

Have you got [Start date] set to date format in the Access Db? If so it doesn't matter what format you send from Excel, Access will convert it to dd/mm/yyyy, you'd need to change the Access side to fix it. Go into table design view and put mmmm yyyy in the format field, that fixes it.

If that does what you want you don't need the format element in the VBA, just send it the date and access will load it fine and show it as mmmm yyyy

It is in fact a date format in Access. I use this to create the table in VBA

With cn
    .Open scn
    .Execute "CREATE TABLE Flatfile ([Tracker Product] text(255) WITH Compression, " & "[Contract ID] text(255) WITH Compression, " & _
             "[Start Date] datetime, " & "[End Date] datetime)"
End With

is there a way to set the correct format directly from the VBA in Excel? ideally i want to automate everything from my macro before actually opening the file in access.

 

[Johnny C]

Hi
There's bound to be. it might not be straightforward though. Once you have created the field, you need to append a property. it's easy in Access but if you're doing it from Excel VBA that gets tricky as normally you would break it down into a number of lines but I don't know how well Excel VBA would handle objects created on different lines of VBA. Now wel I suspect. I would guess a good starting point would be along the lines of

With cn
    .Open scn
    .Execute "CREATE TABLE Flatfile ([Tracker Product] text(255) WITH Compression, " & "[Contract ID] text(255) WITH Compression, " & _
             "[Start Date] datetime, " & "[End Date] datetime)"
     .Execute "Flatfile.Fields(""[Start date]"").Properties.Append Flatfile.Fields(""[Start date]"").CreateProperty(""Format"", dbText, ""mmmm yyyy"")"
     .Execute "Flatfile.Fields(""[End date]"").Properties.Append Flatfile.Fields(""[End date]"").CreateProperty(""Format"", dbText, ""mmmm yyyy"")"
End With

If this isn't going to work - you might need to split the last two statements into SET and APPEND statements which might not work, you may needto consider creating a blank template Db with all the properties set and importing into that and saving as a different name.

You might have more luck on StackOverflow for a query like this.