SQL query works in query form; 'type mismatch error' when called in vba

[allthingsdata28]

this is my vba code assigned to a command button. the sql query works on its own. I get mismatch error when running vba. please help.

Private Sub Refresh_Click()
Dim rvix As String
Dim db As Database


Set db = CurrentDb

db.Execute "Delete * from historical"
 
rvix = "insert into historical (macro_var, date_stamp, value_var)" & _
        "SELECT a.var_id as macro_var, a.qqy as date_stamp, Max(a.value) AS value_var" & _
        "FROM (SELECT var_id, year('var_timestamp') & " / " & datepart('q','var_timestamp') AS qqy, value FROM rrsmtb_t_macro_daily WHERE var_id = 'VIX') AS a" & _
        "GROUP BY a.var_id, a.qqy;"
db.execute rvix

[jimatqsi]

This can't be right
year('var_times tamp')

You're passing a string to the Year function. I suspect you need to lose the ' marks.

Jim

[Rabbit]

Also, your spacing is incorrect, everything is running together on one line. For example:

...
Max(a.value) AS value_var" & _
         "FROM 
...

comes out to Max(a.value) AS value_varFROM when it is evaluated before sending it to the SQL engine

[jimatqsi]

When I am continuing lines of code like that I always end each line with a space before the ending " and I begin the next line with a space after the first ". Just to be sure I don't run into the problem you have in this case.

[allthingsdata28]

this being my first activity on the forum, I was very happy and appreciative with the quick responses. jim; I adjusted the spacing of the query and even built the query in query design(which works) so that the var_timestamp passes as a date value. Still the same mismatch error remains. Here is new code with spacing and query designed.

Private Sub Refresh_Click()
Dim rvix As String
Dim db As Database


Set db = CurrentDb

db.Execute "Delete * from historical"
 
rvix = "insert into historical (macro_var, date_stamp, value_var) " & _
        " SELECT RRSMTB_T_MACRO_DAILY.VAR_ID, Year([var_timestamp]) & " / " & DatePart('q',[var_timestamp]) AS qqy, Max(RRSMTB_T_MACRO_DAILY.VALUE) AS MaxOfVALUE " & _
        " FROM RRSMTB_T_MACRO_DAILY " & _
        " GROUP BY RRSMTB_T_MACRO_DAILY.VAR_ID, Year([var_timestamp]) & " / " & DatePart('q',[var_timestamp]) " & _
        " HAVING (((RRSMTB_T_MACRO_DAILY.VAR_ID)='VIX'));"
        
   
db.Execute rvix
end sub

[jforbes]

When I get stuck like this, I usually try to insert the SQL into a Query Def to see what Access thinks of it.

You can put a breakpoint on line 17 and Execute the code. When the code breaks, open the Immediate Window (ctrl-G). Then type ?rvix and hit Enter into the Immediate Window. This will give you the SQL that you are attempting to execute. Next, copy this SQL onto the clipboard, and create a new SQL Definition. Then paste the SQL into the new definition and attempt to run it and see where Access thinks the error is located.

[Rabbit]

If date_stamp is a date time value, it needs to be surrounded by pound signs. If it's a string, it needs to be surrounded by single quotes.