Date Conversion

[Ginny2222]

Hi
I’m importing a date which is in the format dd.mm.yyyy. I need this field as a date in my database.

The following works in a Query:

LEFT(Import_Table.F6],2)+"/"+MID([Import_Table.F6],4,2)+"/"+RIGHT([Import_Table.F6],4)

When I transfer this to VBA Code though, it doesn’t work.

Dim sql As String
sql = "INSERT INTO TABLE_Namel ( StartDate ) SELECT Import_Table. LEFT(Import_Table.F6],2)&"/"&MID([Import_Table.F6],4,2)&"/"&RIGHT([Import_Table.F6],4) FROM Import_Table WITH OWNERACCESS OPTION;"
DoCmd.RunSQL (sql)

Can anyone put me straight on this?
Help appreciated.
Ginny

 

[gemma-the-husky]

this wont work

you can't use parameters in this way

you have to use set it out in pieces, to make the final string an acceptable sqlstring
also dates have to be masked with # characters

so the string needs to be formed in this way

sqlstrg = ""INSERT INTO TABLE_Namel ( StartDate ) SELECT " & "#" & now format your date & "#" etc

 

[Ginny2222]

Thanks, I'll get working on this.
Appreciate the help
Ginny