I have inherited the code below which basically looks at the date, creates a
series of queries dependant on that date and then combines the results from
these queries into one table for export to Excel.  The data is sourced from
two tables and compares the 'Fund' data with its 'Benchmark' data.  I have
had to change it slightly in that the underlying data used to be sourced from
existing Acces tables but is now sourced from CSV files downloaded from our
mainframe.  These CSV files format the Year and Month as text so within the
calculation I have used Val(data_1.Year) etc to convert them (as I was
getting a Datatype mismatch error).  Since I have made this change I get this
"Run-time error '5'" Invalid procedure call or argument" error and the
'DoCmd.OpenQuery QueryName' line is highlighted.  I can't for the life of me
see what the problem is and it is now starting to really annoy me!  Can
anyone please help?

Function CreateAbsQueries()
   'creates the risk/rtn queries dependant on the date of run
   Dim dbs As Database, qdf As QueryDef, sqlcode As String
   Dim s1, s2, s3, s4, s5 As String
   Dim EndYear, EndMonth, CurrYear, CurrMonth As Integer
   Dim QueryName, Quarter As String
   Dim delay As Integer
1
   On Error GoTo errorhandler
   
   Set dbs = CurrentDb
   
   
   EndYear = Year(Date)
   EndMonth = Month(Date) - 1
   'EndMonth = 1 - PUT THIS BACK IN AFTER Q1

   DoCmd.SetWarnings False
 
   'set period ends by utilising date of run
   Select Case EndMonth
       
       Case 0, 1, 2, 3
           EndYear = EndYear - 1
           Quarter = "Q4"
       Case 4, 5, 6
           Quarter = "Q1"
       Case 7, 8, 9
           Quarter = "Q2"
       Case 10, 11, 12
           Quarter = "Q3"
   End Select

   'loop through and create queries
   For CurrYear = EndYear - 5 To EndYear
       For CurrMonth = 6 To 12 Step 6
           QueryName = "AbsRisk-" & CurrYear & "-" & CurrMonth
       
           s1 = "SELECT data.Fund,
(Exp(Sum(Log(1+[data]![TWR]/100)))^(1/3)-1)*100 AS AbsRtn,
StDev(Sqr(12)*(Log(1+[data]![TWR]/100)))*100 AS AbsRisk, [AbsRtn]/[AbsRisk]
AS IR, Rating([IR]) AS RARA "
           s2 = "FROM (data INNER JOIN Fund_List ON data.Fund =
Fund_List.FundCode) INNER JOIN data AS data_1 ON (data.Month = data_1.Month)
AND (data.Year = data_1.Year) AND (data.IntCatCode = data_1.IntCatCode) AND
(Fund_List.BMCode = data_1.Fund) "
           
           Select Case Quarter
               Case "Q1"
                   If CurrMonth = 12 Then
                       s3 = "WHERE (((val(data_1.Year))= " & CurrYear - 3 &
") AND ((val(data.Month))>3)) OR (((val(data_1.Year))=" & CurrYear - 2 & " Or
(val(data_1.Year))=" & CurrYear - 1 & ")) OR (((val(data_1.Year))=" &
CurrYear & " ) AND ((val(data.Month))<4)) "
                       QueryName = "AbsRisk-" & CurrYear & "-3"
                   Else
                       s3 = "WHERE (((val(data_1.Year))= " & CurrYear - 4 &
") AND ((val(data.Month))>9)) OR (((val(data_1.Year))=" & CurrYear - 3 & " Or
(val(data_1.Year))=" & CurrYear - 2 & ")) OR (((val(data_1.Year))=" &
CurrYear - 1 & " ) AND ((val(data.Month))<10)) "
                       QueryName = "AbsRisk-" & CurrYear - 1 & "-9"
                   End If
               
               Case "Q2"
                   If CurrMonth = 12 Then
                       s3 = "WHERE (((val(data_1.Year))= " & CurrYear - 3 &
") AND ((val(data.Month))>6)) OR (((val(data_1.Year))=" & CurrYear - 2 & " Or
(val(data_1.Year))=" & CurrYear - 1 & ")) OR (((val(data_1.Year))=" &
CurrYear & " ) AND ((val(data.Month))<7)) "
                       QueryName = "AbsRisk-" & CurrYear & "-6"
                   Else
                       s3 = "WHERE (((val(data_1.Year)) = " & CurrYear - 3
& " Or (val(data_1.Year)) = " & CurrYear - 2 & " Or (val(data_1.Year)) = " &
CurrYear - 1 & ")) "
                       QueryName = "AbsRisk-" & CurrYear - 1 & "-12"
                   End If
               
               Case "Q3"
                   If CurrMonth = 12 Then
                       s3 = "WHERE (((val(data_1.Year))= " & CurrYear - 3 &
") AND ((val(data.Month))>9)) OR (((val(data_1.Year))=" & CurrYear - 2 & " Or
(val(data_1.Year))=" & CurrYear - 1 & ")) OR (((val(data_1.Year))=" &
CurrYear & " ) AND ((val(data.Month))<10)) "
                       QueryName = "AbsRisk-" & CurrYear & "-9"
                   Else
                       s3 = "WHERE (((val(data_1.Year))= " & CurrYear - 3 &
") AND ((val(data.Month))>3)) OR (((val(data_1.Year))=" & CurrYear - 2 & " Or
(val(data_1.Year))=" & CurrYear - 1 & ")) OR (((val(data_1.Year))=" &
CurrYear & " ) AND ((val(data.Month))<4)) "
                       QueryName = "AbsRisk-" & CurrYear & "-3"
                   End If
               
               Case "Q4"
                   If CurrMonth = 12 Then
                       s3 = "WHERE (((val(data_1.Year)) = " & CurrYear - 2
& " Or (val(data_1.Year)) = " & CurrYear - 1 & " Or (val(data_1.Year)) = " &
CurrYear & ")) "
                       QueryName = "AbsRisk-" & CurrYear & "-12"
                   Else
                      s3 = "WHERE (((val(data_1.Year))= " & CurrYear - 3 &
") AND ((val(data.Month))>6)) OR (((val(data_1.Year))=" & CurrYear - 2 & " Or
(val(data_1.Year))=" & CurrYear - 1 & ")) OR (((val(data_1.Year))=" &
CurrYear & " ) AND ((val(data.Month))<7)) "
                       QueryName = "AbsRisk-" & CurrYear & "-6"
                   End If
           
           End Select
           
           s4 = "GROUP BY data.Fund "
           s5 = "HAVING (((Count(data.Month))=36)); "
'***** change back to 36 *********
           
           sqlcode = s1 & s2 & s3 & s4 & s5

           Set qdf = dbs.CreateQueryDef(QueryName, sqlcode)
           
       Next CurrMonth
   Next CurrYear
   
   'make query to combine results into 1 extractable table
   Select Case Quarter
       Case "Q1"
           s1 = "SELECT [AbsRisk-" & EndYear & "-3].Fund, [AbsRisk-" &
EndYear & "-3].AbsRisk, [AbsRisk-" & EndYear & "-3].AbsRtn, [AbsRisk-" &
EndYear - 1 & "-9].AbsRisk, [AbsRisk-" & EndYear - 1 & "-9].AbsRtn,
[AbsRisk-" & EndYear - 1 & "-3].AbsRisk, [AbsRisk-" & EndYear - 1 &
"-3].AbsRtn, [AbsRisk-" & EndYear - 2 & "-9].AbsRisk, [AbsRisk-" & EndYear -
2 & "-9].AbsRtn, [AbsRisk-" & EndYear - 2 & "-3].AbsRisk, [AbsRisk-" &
EndYear - 2 & "-3].AbsRtn, [AbsRisk-" & EndYear - 3 & "-9].AbsRisk,
[AbsRisk-" & EndYear - 3 & "-9].AbsRtn, [AbsRisk-" & EndYear - 3 &
"-3].AbsRisk, [AbsRisk-" & EndYear - 3 & "-3].AbsRtn, [AbsRisk-" & EndYear -
4 & "-9].AbsRisk, [AbsRisk-" & EndYear - 4 & "-9].AbsRtn, [AbsRisk-" &
EndYear - 4 & "-3].AbsRisk, [AbsRisk-" & EndYear - 4 & "-3].AbsRtn INTO
[Summary Abs Data for Excel] "
           s2 = "FROM ((((((([AbsRisk-" & EndYear & "-3] LEFT JOIN
[AbsRisk-" & EndYear - 1 & "-9] ON [AbsRisk-" & EndYear & "-3].Fund =
[AbsRisk-" & EndYear - 1 & "-9].Fund) LEFT JOIN [AbsRisk-" & EndYear - 1 &
"-3] ON [AbsRisk-" & EndYear & "-3].Fund = [AbsRisk-" & EndYear - 1 &
"-3].Fund) LEFT JOIN [AbsRisk-" & EndYear - 2 & "-9] ON [AbsRisk-" & EndYear
& "-3].Fund = [AbsRisk-" & EndYear - 2 & "-9].Fund) LEFT JOIN [AbsRisk-" &
EndYear - 2 & "-3] ON [AbsRisk-" & EndYear & "-3].Fund = [AbsRisk-" & EndYear
- 2 & "-3].Fund) LEFT JOIN [AbsRisk-" & EndYear - 3 & "-9] ON [AbsRisk-" &
EndYear & "-3].Fund = [AbsRisk-" & EndYear - 3 & "-9].Fund) LEFT JOIN
[AbsRisk-" & EndYear - 3 & "-3] ON [AbsRisk-" & EndYear & "-3].Fund =
[AbsRisk-" & EndYear - 3 & "-3].Fund) LEFT JOIN [AbsRisk-" & EndYear - 4 &
"-9] ON [AbsRisk-" & EndYear & "-3].Fund = [AbsRisk-" & EndYear - 4 &
"-9].Fund) LEFT JOIN [AbsRisk-" & EndYear - 4 & "-3] ON [AbsRisk-" & EndYear
& "-3].Fund = [AbsRisk-" & EndYear - 4 & "-3].Fund;"
       Case "Q2"
           s1 = "SELECT [AbsRisk-" & EndYear & "-6].Fund, [AbsRisk-" &
EndYear & "-6].AbsRisk, [AbsRisk-" & EndYear & "-6].AbsRtn, [AbsRisk-" &
EndYear - 1 & "-12].AbsRisk, [AbsRisk-" & EndYear - 1 & "-12].AbsRtn,
[AbsRisk-" & EndYear - 1 & "-6].AbsRisk, [AbsRisk-" & EndYear - 1 &
"-6].AbsRtn, [AbsRisk-" & EndYear - 2 & "-12].AbsRisk, [AbsRisk-" & EndYear -
2 & "-12].AbsRtn, [AbsRisk-" & EndYear - 2 & "-6].AbsRisk, [AbsRisk-" &
EndYear - 2 & "-6].AbsRtn, [AbsRisk-" & EndYear - 3 & "-12].AbsRisk,
[AbsRisk-" & EndYear - 3 & "-12].AbsRtn, [AbsRisk-" & EndYear - 3 &
"-6].AbsRisk, [AbsRisk-" & EndYear - 3 & "-6].AbsRtn, [AbsRisk-" & EndYear -
4 & "-12].AbsRisk, [AbsRisk-" & EndYear - 4 & "-12].AbsRtn, [AbsRisk-" &
EndYear - 4 & "-6].AbsRisk, [AbsRisk-" & EndYear - 4 & "-6].AbsRtn INTO
[Summary Abs Data for Excel] "
           s2 = "FROM ((((((([AbsRisk-" & EndYear & "-6] LEFT JOIN
[AbsRisk-" & EndYear - 1 & "-12] ON [AbsRisk-" & EndYear & "-6].Fund =
[AbsRisk-" & EndYear - 1 & "-12].Fund) LEFT JOIN [AbsRisk-" & EndYear - 1 &
"-6] ON [AbsRisk-" & EndYear & "-6].Fund = [AbsRisk-" & EndYear - 1 &
"-6].Fund) LEFT JOIN [AbsRisk-" & EndYear - 2 & "-12] ON [AbsRisk-" & EndYear
& "-6].Fund = [AbsRisk-" & EndYear - 2 & "-12].Fund) LEFT JOIN [AbsRisk-" &
EndYear - 2 & "-6] ON [AbsRisk-" & EndYear & "-6].Fund = [AbsRisk-" & EndYear
- 2 & "-6].Fund) LEFT JOIN [AbsRisk-" & EndYear - 3 & "-12] ON [AbsRisk-" &
EndYear & "-6].Fund = [AbsRisk-" & EndYear - 3 & "-12].Fund) LEFT JOIN
[AbsRisk-" & EndYear - 3 & "-6] ON [AbsRisk-" & EndYear & "-6].Fund =
[AbsRisk-" & EndYear - 3 & "-6].Fund) LEFT JOIN [AbsRisk-" & EndYear - 4 &
"-12] ON [AbsRisk-" & EndYear & "-6].Fund = [AbsRisk-" & EndYear - 4 &
"-12].Fund) LEFT JOIN [AbsRisk-" & EndYear - 4 & "-6] ON [AbsRisk-" & EndYear
& "-6].Fund = [AbsRisk-" & EndYear - 4 & "-6].Fund;"
       Case "Q3"
           s1 = "SELECT [AbsRisk-" & EndYear & "-9].Fund, [AbsRisk-" &
EndYear & "-9].AbsRisk, [AbsRisk-" & EndYear & "-9].AbsRtn, [AbsRisk-" &
EndYear & "-3].AbsRisk, [AbsRisk-" & EndYear & "-3].AbsRtn, [AbsRisk-" &
EndYear - 1 & "-9].AbsRisk, [AbsRisk-" & EndYear - 1 & "-9].AbsRtn,
[AbsRisk-" & EndYear - 1 & "-3].AbsRisk, [AbsRisk-" & EndYear - 1 &
"-3].AbsRtn, [AbsRisk-" & EndYear - 2 & "-9].AbsRisk, [AbsRisk-" & EndYear -
2 & "-9].AbsRtn, [AbsRisk-" & EndYear - 2 & "-3].AbsRisk, [AbsRisk-" &
EndYear - 2 & "-3].AbsRtn, [AbsRisk-" & EndYear - 3 & "-9].AbsRisk,
[AbsRisk-" & EndYear - 3 & "-9].AbsRtn, [AbsRisk-" & EndYear - 3 &
"-3].AbsRisk, [AbsRisk-" & EndYear - 3 & "-3].AbsRtn, [AbsRisk-" & EndYear -
4 & "-9].AbsRisk, [AbsRisk-" & EndYear - 4 & "-9].AbsRtn INTO [Summary Abs
Data for Excel] "
           s2 = "FROM ((((((([AbsRisk-" & EndYear & "-9] LEFT JOIN
[AbsRisk-" & EndYear & "-3] ON [AbsRisk-" & EndYear & "-9].Fund = [AbsRisk-"
& EndYear & "-3].Fund) LEFT JOIN [AbsRisk-" & EndYear - 1 & "-9] ON
[AbsRisk-" & EndYear & "-9].Fund = [AbsRisk-" & EndYear - 1 & "-9].Fund) LEFT
JOIN [AbsRisk-" & EndYear - 1 & "-3] ON [AbsRisk-" & EndYear & "-9].Fund =
[AbsRisk-" & EndYear - 1 & "-3].Fund) LEFT JOIN [AbsRisk-" & EndYear - 2 &
"-9] ON [AbsRisk-" & EndYear & "-9].Fund = [AbsRisk-" & EndYear - 2 &
"-9].Fund) LEFT JOIN [AbsRisk-" & EndYear - 2 & "-3] ON [AbsRisk-" & EndYear
& "-9].Fund = [AbsRisk-" & EndYear - 2 & "-3].Fund) LEFT JOIN [AbsRisk-" &
EndYear - 3 & "-9] ON [AbsRisk-" & EndYear & "-9].Fund = [AbsRisk-" & EndYear
- 3 & "-9].Fund) LEFT JOIN [AbsRisk-" & EndYear - 3 & "-3] ON [AbsRisk-" &
EndYear & "-9].Fund = [AbsRisk-" & EndYear - 3 & "-3].Fund) LEFT JOIN
[AbsRisk-" & EndYear - 4 & "-9] ON [AbsRisk-" & EndYear & "-9].Fund =
[AbsRisk-" & EndYear - 4 & "-9].Fund;"
       Case "Q4"
           s1 = "SELECT [AbsRisk-" & EndYear & "-12].Fund, [AbsRisk-" &
EndYear & "-12].AbsRisk, [AbsRisk-" & EndYear & "-12].AbsRtn, [AbsRisk-" &
EndYear & "-6].AbsRisk, [AbsRisk-" & EndYear & "-6].AbsRtn, [AbsRisk-" &
EndYear - 1 & "-12].AbsRisk, [AbsRisk-" & EndYear - 1 & "-12].AbsRtn,
[AbsRisk-" & EndYear - 1 & "-6].AbsRisk, [AbsRisk-" & EndYear - 1 &
"-6].AbsRtn, [AbsRisk-" & EndYear - 2 & "-12].AbsRisk, [AbsRisk-" & EndYear -
2 & "-12].AbsRtn, [AbsRisk-" & EndYear - 2 & "-6].AbsRisk, [AbsRisk-" &
EndYear - 2 & "-6].AbsRtn, [AbsRisk-" & EndYear - 3 & "-12].AbsRisk,
[AbsRisk-" & EndYear - 3 & "-12].AbsRtn, [AbsRisk-" & EndYear - 3 &
"-6].AbsRisk, [AbsRisk-" & EndYear - 3 & "-6].AbsRtn, [AbsRisk-" & EndYear -
4 & "-12].AbsRisk, [AbsRisk-" & EndYear - 4 & "-12].AbsRtn INTO [Summary Abs
Data for Excel] "
           s2 = "FROM ((((((([AbsRisk-" & EndYear & "-12] LEFT JOIN
[AbsRisk-" & EndYear & "-6] ON [AbsRisk-" & EndYear & "-12].Fund = [AbsRisk-"
& EndYear & "-6].Fund) LEFT JOIN [AbsRisk-" & EndYear - 1 & "-12] ON
[AbsRisk-" & EndYear & "-12].Fund = [AbsRisk-" & EndYear - 1 & "-12].Fund)
LEFT JOIN [AbsRisk-" & EndYear - 1 & "-6] ON [AbsRisk-" & EndYear &
"-12].Fund = [AbsRisk-" & EndYear - 1 & "-6].Fund) LEFT JOIN [AbsRisk-" &
EndYear - 2 & "-12] ON [AbsRisk-" & EndYear & "-12].Fund = [AbsRisk-" &
EndYear - 2 & "-12].Fund) LEFT JOIN [AbsRisk-" & EndYear - 2 & "-6] ON
[AbsRisk-" & EndYear & "-12].Fund = [AbsRisk-" & EndYear - 2 & "-6].Fund)
LEFT JOIN [AbsRisk-" & EndYear - 3 & "-12] ON [AbsRisk-" & EndYear &
"-12].Fund = [AbsRisk-" & EndYear - 3 & "-12].Fund) LEFT JOIN [AbsRisk-" &
EndYear - 3 & "-6] ON [AbsRisk-" & EndYear & "-12].Fund = [AbsRisk-" &
EndYear - 3 & "-6].Fund) LEFT JOIN [AbsRisk-" & EndYear - 4 & "-12] ON
[AbsRisk-" & EndYear & "-12].Fund = [AbsRisk-" & EndYear - 4 & "-12].Fund;"
   End Select
   sqlcode = s1 & s2
   QueryName = "AbsCombineRiskReturn"
   DoCmd.DeleteObject acQuery, QueryName
   
   Set qdf = dbs.CreateQueryDef(QueryName, sqlcode)
   DoCmd.OpenQuery QueryName

   DoCmd.SetWarnings True
 
   MsgBox ("Returns Complete.")
   Exit Function
errorhandler:
   Select Case Err.Number
       Case 3011
           Resume Next
       Case 3012
          ' For delay = 1 To 30000
          ' Next delay
           DoCmd.DeleteObject acQuery, QueryName
           Resume Next
       Case 3078
           GoTo 1
       Case Else
           MsgBox (Err.Number & Err.Description)
   End Select
   
End Function