append records with text converted to a date

[Delindan]

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!

 

[PHV]

DoCmd.RunSQL "INSERT INTO Currentsalary ([Personnel Number],[Annual Salary],FXRate,[Date],DateAdded) SELECT [Personnel Number],[Annual Salary],([Annual Salary]-USD)/[Annual Salary],#" & Left(strDate,3) & "01/" & Right(strDate,4) & "#,Date() FROM [Temporary];"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Delindan]

I tried your syntax (as well as about a million others) and still having an issue. So I thought I would put the string together as I'm grabbing the info and then did a debug.print of the strdate to see how it came out and it looked just like I wanted...04/01/2012. When I went to do my insert statement...DoCmd.RunSQL "INSERT INTO Currentsalary ([Personnel Number],[Annual Salary],FXRate,[Date],DateAdded) SELECT [Personnel Number],[Annual Salary],([Annual Salary]-USD)/[Annual Salary], strdate, Date() FROM [Temporary];" It acted like it didn't know what strdate was although when I did a debug.print strdate it knew it. It is almost like it was looking for that field name in the temporary table. Any suggestions? Thanks!

 

[PHV]

DoCmd.RunSQL "INSERT INTO Currentsalary ([Personnel Number],[Annual Salary],FXRate,[Date],DateAdded) SELECT [Personnel Number],[Annual Salary],([Annual Salary]-USD)/[Annual Salary], " & strdate & ", Date() FROM [Temporary];"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Delindan]

Thanks for the suggestion! I tried this and didn't get any errors however when I look at the records that were appended they are a time 12:02 rather than a date.

 

[Delindan]

OMG...I figured it out. Your syntax worked....however I had to change how I was grabbing/saving the strDate. I added a line that added # signs to the string:

strDate = Left(RSDate("F2"), 3) & "01/" & Right(RSDate("F2"), 4)
strDate = "#" & strDate & "#"

Yeah...thanks for the help!