[dodyda]

Hello everyone, I'm using an Access database with DAO, I used to create some tables at run-time using the method DB.Execute with SQL and it was all fine until I had a problem with date fields when I was exporting data to Excel file, Excel is switching the "dd-mm-yyyy" format to "mm-dd-yyyy" format in some cells and not all, I tried all the possible solutions like the Format() function, change the date format from the control panel and changing the cell format from within Excel and the problem still exists, I thought of another solution which is to change the database field format from "dd-mm-yyyy" to "dd-mmmm-yyyy". Now the question is how to create table at run-time that has a date type field that has "dd-mmmm-yyyy" format, I found another method as follows but couldn't make it

Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    
    'Initialize the Contractor table.
    Set db = CurrentDb()
    Set tdf = db.CreateTableDef("tblDaoContractor")
    
    'Specify the fields.
    With tdf   
        'Text field: maximum 30 characters, and required.
        Set fld = .CreateField("Surname", dbText, 30)
        fld.Required = True
        .Fields.Append fld
        
        'Text field: maximum 20 characters.
        .Fields.Append .CreateField("FirstName", dbText, 20)
        
        'Yes/No field.
        .Fields.Append .CreateField("Inactive", dbBoolean)
        
        'Currency field.
        .Fields.Append .CreateField("HourlyFee", dbCurrency)
        
        'Number field.
        .Fields.Append .CreateField("PenaltyRate", dbDouble)
        
        'Date/Time field with validation rule.
        Set fld = .CreateField("BirthDate", dbDate)
        fld.ValidationRule = "Is Null Or <=Date()"
        fld.ValidationText = "Birth date cannot be future."
        .Fields.Append fld
        
        'Memo field.
        .Fields.Append .CreateField("Notes", dbMemo)
        
        'Hyperlink field: memo with the attribute set.
        Set fld = .CreateField("Web", dbMemo)
        fld.Attributes = dbHyperlinkField + dbVariableField
        .Fields.Append fld
    End With
    
    'Save the Contractor table.
    db.TableDefs.Append tdf
    Set fld = Nothing
    Set tdf = Nothing

THANKS IN ADVANCE

[andrewsw]

I don't think changing the date format in Access will help; just use the dbDate data-type.

Show the code that is actually causing the issue. Presumably, this is at the point that you export the data to Excel.

The accepted common format for dates between Access and Excel is mm/dd/yyyy so you may need to use something like #mm/dd/yyyy# just before you export the dates.

BTW I would, personally, avoid Memo fields. If I needed to store a large body of text then I would store a hyperlink or file location as a text field and programmatically open the file or follow the link. Besides which, Memo fields don't display in DataGridViews by default.

This post has been edited by andrewsw: 27 January 2013 - 08:40 AM

[andrewsw]

Well, in answer to your question, the Format property needs to be created after the field and table have been appended - but this is not the solution to your problem:

Sub CreateNewOne()
    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Dim fd As DAO.Field
    Dim pr As DAO.Property

    Set db = CurrentDb
    ' Create table
    Set td = db.CreateTableDef("tblThisOne")
    ' Create field
    Set fd = td.CreateField("AField", dbDate)
    ' Append field
    td.Fields.Append fd
    ' Append table
    db.TableDefs.Append td
    ' Create property
    Set pr = fd.CreateProperty("Format", dbText, "mm\/dd\/yyyy")
    ' Append property
    fd.Properties.Append pr
End Sub

[dodyda]

Thank you so much, that was really helpful , I will take the 2 suggestion in consideration
Thanks again