Run-time error '5' : Invalid procedure call or argument

A

Alpur

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
 
A

Alpur

If it helps, I have had a further look and have narrowed the problem down to
the query "AbsRisk-2005-12" which is created as all others created will run
on their own. The actual query is being created and the SQL coding looks Ok
but when I try running it I am getting an Invalid Procedure Call error
message.

Thanks,

Al.

Alpur said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Another **** Year to Date Report Question 3
type mismatch error in macro in excel 0
A Year to Date Report Question 2
Returning Empty Field 0
Query Help 0
Help with Control Source calculation in report 1
Help with calculation........ 1
Two Q's: Trouble Closing Excel & run-Time 462 5

Top