Convert string to date using sql and vba

Question

I have the query below in access and wanted to use it in VBA...

SQL = "UPDATE Sales SET Sales.order_date = CDate(Right([data],2)+" / "+Mid([data],5,2)+" / "+Left([data],4));"

I keep getting error due to the way I am building the string to then execute.

I have tried to put the

& "/" &

to build the string but get an error when executing the SQL...

How should I build the string?

Thanks!!!

Gustav · Accepted Answer · 2015-03-21 13:04:03Z

1

You would use DateSerial for this if data is a field of your table:

SQL = "UPDATE Sales SET Sales.order_date = DateSerial(Left([data],4), Mid([data],5,2), Right([data],2))"

answered Mar 21, 2015 at 13:04

Gustav

56.5k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user2950162 · Accepted Answer · 2015-03-21 13:00:32Z

0

just escaped the / as '/' and worked...

SQL = "UPDATE Sales SET Sales.order_date = CDate(Right([data],2)+ '/' + Mid([data],5,2) + '/' + Left([data],4));"

answered Mar 21, 2015 at 13:00

user2950162

1,0215 gold badges15 silver badges29 bronze badges