I have a sql command I'm trying to execute and can't get the syntax correct. I have a temporary variable that is storing a text date (format '04/2012') and would like that converted to a date when it appends. What I have is as follows:
DoCmd.RunSQL "INSERT INTO Currentsalary ( [Personnel Number], [Annual Salary], FXRate, Date, DateAdded ) SELECT Temporary.[Personnel Number], Temporary.[Annual Salary], [Temporary]![Annual Salary - USD]/[Temporary]![Annual Salary] AS Expr1, #&(left(strDate,3)&'01/'&right(strDate,4))&#, date() FROM [Temporary];"
Of course the part I'm having trouble with is #&(left(strDate,3)&'01/'&right(strDate,4))&#
It's not working so obviously this isn't correct but can anyone help with the syntax?
Thanks!
DoCmd.RunSQL "INSERT INTO Currentsalary ( [Personnel Number], [Annual Salary], FXRate, Date, DateAdded ) SELECT Temporary.[Personnel Number], Temporary.[Annual Salary], [Temporary]![Annual Salary - USD]/[Temporary]![Annual Salary] AS Expr1, #&(left(strDate,3)&'01/'&right(strDate,4))&#, date() FROM [Temporary];"
Of course the part I'm having trouble with is #&(left(strDate,3)&'01/'&right(strDate,4))&#
It's not working so obviously this isn't correct but can anyone help with the syntax?
Thanks!