Changing the date from Excel to Access

[Philosophaie]

I have in a cell in Excel: MM/DD/YYYY

I want to add it to my Access database as: YYYY/MM/DD

This is what I have so far in Excel VBA. It is giving me a date error. I have tried both as MM/DD/YYYY still did not work.

    n=1
    accDateSub="01/01/2011"
    Dim conn As New ADODB.Connection
    conn.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\path\file1.accdb;"
    thisSQL = "INSERT INTO [Table1] VALUES (" & n & ", #" & Format(accDateSub, "yyyy/mm/dd")  & "#);"
    conn.Execute CommandText:=thisSQL

[June7]

Dates in Access date/time field are saved as a long integer. The integer translates to mm/dd/yyyy. You can apply formatting to the field to display date in other configurations but the data remains unchanged.

Format function results in a string value, not a date or number but concatenation with # character should convert to date of mm/dd/yyyy and save. However, since you encounter issue, don't apply formatting to the import, just save the data and apply formatting in forms and reports.

[Philosophaie]

Changed it to this but error"

Data type mismatch in criteria expression.

thisSQL = "INSERT INTO [Table1] VALUES (" & n & ", '" & accDateSub & "');"

[John_G]

Hi -

Your date string needs to delimited with "#", not single quotes:

thisSQL = "INSERT INTO [Table1] VALUES (" & n & ", #" & accDateSub & "#);"

In your original post, there are two things wrong.
First, accDateSub = "01/01/2012" does not result in a date in accDateSub - accDateSub contains a string which happens to look like a date, but you cannot use it as a date. To put a date into accDateSub, use accDateSub = #01/01/2012#

Once you do that, if you change your format to
Format(accDateSub, "mm/yy/dddd")

it should work.

John

[June7]

Yes, still need the # delimiter, just not the Format function.

@John, "mm/yy/dddd" doesn't make sense, do you mean "mm/dd/yyyy"?

[John_G]

Hi June7 -

Thanks; yes I do mean "mm/dd/yyyy" !!

Must be an age thing... sigh!

John

[Philosophaie]

if you extract:

accDateSub = cells(1,1)

will it not come out as 1/1/2012 which throws off the "#" format making this not work:

thisSQL = "INSERT INTO [Table1] VALUES (" & n & ", #" & accDateSub & "#);"

It gives the error:

Syntax error in date in query expression'#'.

[June7]

The placeholder 0 should not be an issue.

Sorry, should have looked at the entire SQL more carefully. If you don't name the fields, data will go into table left to right. Safest to name the fields. Otherwise, I don't see any syntax error.

thisSQL = "INSERT INTO [Table1](fieldname1, fileldname2) VALUES (" & n & ", #" & accDateSub & "#);"

What type is fieldname1?

Also just hit me you are pushing this data to Access by code in Excel. Not something I have done. I've always had Access pull from Excel.

Show the complete procedure. Want to provide the Excel for analysis? Attach file to post.