G
Guest
I had the following code give me an error on the final line (I've only copied
the code up to the run time error) "Type mismatch error 13". The code work
fine in access 2003 but I got this error after converting to access 2007.
Private Sub cmdWeek54_final_Click()
txtTimeStart.Value = Now
Loops = Me.txtLoops.Value
EndSeason = txtEndSeason.Value
BeginSeason = txtSeason.Value
txtTimeFinished.Value = Now
txtTotalTime.Value = txtTimeFinished.Value - txtTimeStart.Value
Text_LoopNo.Value = "Starting"
Me.Repaint
DoEvents
counter = 0
' strSql = "DELETE * FROM [Filter Parameters]"
'CurrentDb.Execute strSql, dbFailOnError
'BEGIN MAKE QUERY "153i Quality Scores"
Dim db153i As DAO.Database
Dim qdf153i As DAO.QueryDef
Set db153i = CurrentDb()
' Load querydef if it exists; create it if it doesn't.
'On Error Resume Next ' disable error-handling
Set qdf153i = db153i.QueryDefs("153i Quality Scores")
'On Error GoTo Err_Handler ' establish error-handling
If qdf153i Is Nothing Then
Set qdf153i = db153i.CreateQueryDef("153i Quality Scores")
End If
' Set the query's SQL
qdf153i.SQL = "SELECT 54 AS Week, [153g Quality Scores Union Query].Season,
[153g Quality Scores Union Query].Home, +([153g Quality Scores Union
Query]![Sum Of Sum Of Home Margin Performance]/[153g Quality Scores Union
Query]![Sum Of Count Of 153c Quality Scores])" & _
"AS [Avg Of Avg Of Home Modified Score], +([153g Quality Scores Union
Query]![Sum Of Sum Of Home Win-Loss Performance]/[153g Quality Scores Union
Query]![Sum Of Count Of 153c Quality Scores]) AS [Avg Of Avg Of Home Modified
Score Win-Loss], " & _
"+([153g Quality Scores Union Query]![Sum Of Sum Of Home Log
Performance]/[153g Quality Scores Union Query]![Sum Of Count Of 153c Quality
Scores]) AS [Avg Of Avg Of Home Modified Log Score]FROM [153g Quality Scores
Union Query];"
'BEGIN MAKE QUERY "Scores-Win Loss"
Set db = CurrentDb()
' Load querydef if it exists; create it if it doesn't.
'On Error Resume Next ' disable error-handling
Set qdf = db.QueryDefs("Scores-Win Loss")
'On Error GoTo Err_Handler ' establish error-handling
If qdf Is Nothing Then
Set qdf = db.CreateQueryDef("Scores-Win Loss")
End If
' Set the query's SQL
qdf.SQL = "SELECT Scores.ID, Scores.Date, Scores.Season, Scores.Visitor,
Scores.[Visitor Score], Scores.Home, Scores.[Home Score], Scores.Margin,
Scores.Nuetral, Scores.Location, Scores.Location2, Scores.Line," & _
"IIf(Scores!Date-(Now()-0)>0,1,0) AS [Future Game],
IIf(Scores!Margin=0,0,((Scores!Margin)/(Abs(Scores!Margin)))) AS [Win-Loss],
IIf(Round(((((Scores!Date-12/31/1900)/365.25)-(Int((Scores!Date-12/31/1900)/365.25)))*12),1)>9.3,1," & _
"IIf(Round(((((Scores!Date-12/31/1900)/365.25)-(Int((Scores!Date-12/31/1900)/365.25)))*12),1)<1,1,-1))
AS [September Game],
IIf(Round(((((Scores!Date-12/31/1900)/365.25)-(Int((Scores!Date-12/31/1900)/365.25)))*12),1)>11.2,1," & _
"IIf(Round(((((Scores!Date-12/31/1900)/365.25)-(Int((Scores!Date-12/31/1900)/365.25)))*12),1)<1,1,-1))
AS [Bowl Game], 1+(([Home Score]+10)/([Home Score]+[Visitor Score]+20)) AS
[Percent Margin]," & _
"IIf(Abs([Margin])=0,0,(Log(Abs([Margin])))*([Margin]/(Abs([Margin])))) AS
[Adj Log Margin]," & _
"IIf(Scores!Nuetral=0,Scores!Margin-Coefficients![Home Field Adv
Margin],Scores!Margin) AS [Adjusted Margin],
IIf(Scores!Nuetral=0,[Win-Loss]-Coefficients![Home Field
AdvWin-Loss],[Win-Loss])" & _
"AS [Adjusted Margin Win-Loss], [School Season Division].Division," & _
"[School Season Division_1].Division, [Date] & RTrim(Scores!Home) &
RTrim(Scores!Visitor) AS Game, Format(Scores.Date,'ww') AS Week " & _
"FROM Coefficients, [School Season Division] INNER JOIN ([School Season
Division] AS [School Season Division_1] " & _
"INNER JOIN Scores ON ([School Season Division_1].Season = Scores.Season)
AND ([School Season Division_1].Home = Scores.Home)) " & _
"ON ([School Season Division].Home = Scores.Visitor) AND ([School Season
Division].Season = Scores.Season)" & _
"WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And (Scores.Season)<="
& Me.txtEndSeason.Value & "));"
If (IsNull(Me.txtSeason) Or IsNull(Me.txtEndSeason)) Then
MsgBox "Both years required."
Else
strSql = "DELETE * FROM [111 Avg Adjusted Margin DB]"
CurrentDb.Execute strSql, dbFailOnError
strSql = "INSERT INTO [111 Avg Adjusted Margin DB] SELECT [111 Avg
Adjusted Margin].* FROM [111 Avg Adjusted Margin] WHERE [111 Avg Adjusted
Margin].[Season] >=" & Me.txtSeason & ";"
CurrentDb.Execute strSql, dbFailOnError
txtTimeFinished.Value = Now
txtTotalTime.Value = txtTimeFinished.Value - txtTimeStart.Value
Text_LoopNo.Value = "111 Done"
Me.Repaint
DoEvents
strSql = "DELETE * FROM [116 Opp Avg Margin DB]"
CurrentDb.Execute strSql, dbFailOnError
strSql = "INSERT INTO [116 Opp Avg Margin DB] SELECT [116 Opp Avg
Margin].* FROM [116 Opp Avg Margin]WHERE [116 Opp Avg Margin].[Season] <=" &
Me.txtEndSeason & ";"
CurrentDb.Execute strSql, dbFailOnError
txtTimeFinished.Value = Now
txtTotalTime.Value = txtTimeFinished.Value - txtTimeStart.Value
Text_LoopNo.Value = "116 Done"
Me.Repaint
DoEvents
strSql = "DELETE * FROM [122 Power Rankings DB]"
CurrentDb.Execute strSql, dbFailOnError
strSql = "INSERT INTO [122 Power Rankings DB] SELECT [122 Power
Rankings].* FROM [122 Power Rankings]"
CurrentDb.Execute strSql, dbFailOnError
txtTimeFinished.Value = Now
txtTotalTime.Value = txtTimeFinished.Value - txtTimeStart.Value
Text_LoopNo.Value = "122 Done"
Me.Repaint
DoEvents
Dim tdfNew As TableDef, rs As Recordset
Dim wrkDefault As Workspace
Dim dbsTemp As Database, strTempDatabase As String
Dim strTableName As String
Dim strTableName1 As String
Dim strTableName2 As String
' Get default Workspace.
Set wrkDefault = DBEngine.Workspaces(0)
strTempDatabase = Left$(CurrentDb.Name, Len(CurrentDb.Name) - 4) & "
temp.mdb"
' Make sure there isn't already a file with the name of
' the new database.
If Dir(strTempDatabase) <> "" Then Kill strTempDatabase
End If
'Create a new temp database
Set dbsTemp = wrkDefault.CreateDatabase(strTempDatabase, dbLangGeneral)
strTableName = "143 Quality Scores Union Query DB"
strTableName1 = "153 Quality Scores Union Query DB"
strTableName2 = "163 Quality Scores Union Query DB"
'strBracketedTableName = "[" & strTableName & "]"
' Delete the link to the temp table if it exists
If TableExists(strTableName) Then
CurrentDb.TableDefs.Delete strTableName
End If
If TableExists(strTableName1) Then
CurrentDb.TableDefs.Delete strTableName1
End If
If TableExists(strTableName2) Then
CurrentDb.TableDefs.Delete strTableName2
End If
' Create the temp table
Set tdfNew = dbsTemp.CreateTableDef(strTableName)
With tdfNew
.Fields.Append .CreateField("Week", dbInteger)
.Fields.Append .CreateField("Season", dbInteger)
.Fields.Append .CreateField("Home", dbText)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Score",
dbDouble)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Score
Win-Loss", dbDouble)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Log Score",
dbDouble)
dbsTemp.TableDefs.Append tdfNew
End With
Set tdfNew1 = dbsTemp.CreateTableDef(strTableName1)
With tdfNew1
.Fields.Append .CreateField("Week", dbInteger)
.Fields.Append .CreateField("Season", dbInteger)
.Fields.Append .CreateField("Home", dbText)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Score",
dbDouble)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Score
Win-Loss", dbDouble)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Log Score",
dbDouble)
dbsTemp.TableDefs.Append tdfNew1
End With
Set tdfNew2 = dbsTemp.CreateTableDef(strTableName2)
With tdfNew2
.Fields.Append .CreateField("Week", dbInteger)
.Fields.Append .CreateField("Season", dbInteger)
.Fields.Append .CreateField("Home", dbText)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Score",
dbDouble)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Score
Win-Loss", dbDouble)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Log Score",
dbDouble)
dbsTemp.TableDefs.Append tdfNew2
End With
dbsTemp.TableDefs.Refresh
Dim tdfLinked As TableDef
' Link to the Import tables in the temp MDB
Set tdfLinked = CurrentDb.CreateTableDef(strTableName)
tdfLinked.Connect = ";DATABASE=" & strTempDatabase
tdfLinked.SourceTableName = strTableName
CurrentDb.TableDefs.Append tdfLinked
CurrentDb.TableDefs.Refresh
RefreshDatabaseWindow
Set rs = CurrentDb.OpenRecordset(strTableName, dbOpenDynaset,
dbAppendOnly)
the code up to the run time error) "Type mismatch error 13". The code work
fine in access 2003 but I got this error after converting to access 2007.
Private Sub cmdWeek54_final_Click()
txtTimeStart.Value = Now
Loops = Me.txtLoops.Value
EndSeason = txtEndSeason.Value
BeginSeason = txtSeason.Value
txtTimeFinished.Value = Now
txtTotalTime.Value = txtTimeFinished.Value - txtTimeStart.Value
Text_LoopNo.Value = "Starting"
Me.Repaint
DoEvents
counter = 0
' strSql = "DELETE * FROM [Filter Parameters]"
'CurrentDb.Execute strSql, dbFailOnError
'BEGIN MAKE QUERY "153i Quality Scores"
Dim db153i As DAO.Database
Dim qdf153i As DAO.QueryDef
Set db153i = CurrentDb()
' Load querydef if it exists; create it if it doesn't.
'On Error Resume Next ' disable error-handling
Set qdf153i = db153i.QueryDefs("153i Quality Scores")
'On Error GoTo Err_Handler ' establish error-handling
If qdf153i Is Nothing Then
Set qdf153i = db153i.CreateQueryDef("153i Quality Scores")
End If
' Set the query's SQL
qdf153i.SQL = "SELECT 54 AS Week, [153g Quality Scores Union Query].Season,
[153g Quality Scores Union Query].Home, +([153g Quality Scores Union
Query]![Sum Of Sum Of Home Margin Performance]/[153g Quality Scores Union
Query]![Sum Of Count Of 153c Quality Scores])" & _
"AS [Avg Of Avg Of Home Modified Score], +([153g Quality Scores Union
Query]![Sum Of Sum Of Home Win-Loss Performance]/[153g Quality Scores Union
Query]![Sum Of Count Of 153c Quality Scores]) AS [Avg Of Avg Of Home Modified
Score Win-Loss], " & _
"+([153g Quality Scores Union Query]![Sum Of Sum Of Home Log
Performance]/[153g Quality Scores Union Query]![Sum Of Count Of 153c Quality
Scores]) AS [Avg Of Avg Of Home Modified Log Score]FROM [153g Quality Scores
Union Query];"
'BEGIN MAKE QUERY "Scores-Win Loss"
Set db = CurrentDb()
' Load querydef if it exists; create it if it doesn't.
'On Error Resume Next ' disable error-handling
Set qdf = db.QueryDefs("Scores-Win Loss")
'On Error GoTo Err_Handler ' establish error-handling
If qdf Is Nothing Then
Set qdf = db.CreateQueryDef("Scores-Win Loss")
End If
' Set the query's SQL
qdf.SQL = "SELECT Scores.ID, Scores.Date, Scores.Season, Scores.Visitor,
Scores.[Visitor Score], Scores.Home, Scores.[Home Score], Scores.Margin,
Scores.Nuetral, Scores.Location, Scores.Location2, Scores.Line," & _
"IIf(Scores!Date-(Now()-0)>0,1,0) AS [Future Game],
IIf(Scores!Margin=0,0,((Scores!Margin)/(Abs(Scores!Margin)))) AS [Win-Loss],
IIf(Round(((((Scores!Date-12/31/1900)/365.25)-(Int((Scores!Date-12/31/1900)/365.25)))*12),1)>9.3,1," & _
"IIf(Round(((((Scores!Date-12/31/1900)/365.25)-(Int((Scores!Date-12/31/1900)/365.25)))*12),1)<1,1,-1))
AS [September Game],
IIf(Round(((((Scores!Date-12/31/1900)/365.25)-(Int((Scores!Date-12/31/1900)/365.25)))*12),1)>11.2,1," & _
"IIf(Round(((((Scores!Date-12/31/1900)/365.25)-(Int((Scores!Date-12/31/1900)/365.25)))*12),1)<1,1,-1))
AS [Bowl Game], 1+(([Home Score]+10)/([Home Score]+[Visitor Score]+20)) AS
[Percent Margin]," & _
"IIf(Abs([Margin])=0,0,(Log(Abs([Margin])))*([Margin]/(Abs([Margin])))) AS
[Adj Log Margin]," & _
"IIf(Scores!Nuetral=0,Scores!Margin-Coefficients![Home Field Adv
Margin],Scores!Margin) AS [Adjusted Margin],
IIf(Scores!Nuetral=0,[Win-Loss]-Coefficients![Home Field
AdvWin-Loss],[Win-Loss])" & _
"AS [Adjusted Margin Win-Loss], [School Season Division].Division," & _
"[School Season Division_1].Division, [Date] & RTrim(Scores!Home) &
RTrim(Scores!Visitor) AS Game, Format(Scores.Date,'ww') AS Week " & _
"FROM Coefficients, [School Season Division] INNER JOIN ([School Season
Division] AS [School Season Division_1] " & _
"INNER JOIN Scores ON ([School Season Division_1].Season = Scores.Season)
AND ([School Season Division_1].Home = Scores.Home)) " & _
"ON ([School Season Division].Home = Scores.Visitor) AND ([School Season
Division].Season = Scores.Season)" & _
"WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And (Scores.Season)<="
& Me.txtEndSeason.Value & "));"
If (IsNull(Me.txtSeason) Or IsNull(Me.txtEndSeason)) Then
MsgBox "Both years required."
Else
strSql = "DELETE * FROM [111 Avg Adjusted Margin DB]"
CurrentDb.Execute strSql, dbFailOnError
strSql = "INSERT INTO [111 Avg Adjusted Margin DB] SELECT [111 Avg
Adjusted Margin].* FROM [111 Avg Adjusted Margin] WHERE [111 Avg Adjusted
Margin].[Season] >=" & Me.txtSeason & ";"
CurrentDb.Execute strSql, dbFailOnError
txtTimeFinished.Value = Now
txtTotalTime.Value = txtTimeFinished.Value - txtTimeStart.Value
Text_LoopNo.Value = "111 Done"
Me.Repaint
DoEvents
strSql = "DELETE * FROM [116 Opp Avg Margin DB]"
CurrentDb.Execute strSql, dbFailOnError
strSql = "INSERT INTO [116 Opp Avg Margin DB] SELECT [116 Opp Avg
Margin].* FROM [116 Opp Avg Margin]WHERE [116 Opp Avg Margin].[Season] <=" &
Me.txtEndSeason & ";"
CurrentDb.Execute strSql, dbFailOnError
txtTimeFinished.Value = Now
txtTotalTime.Value = txtTimeFinished.Value - txtTimeStart.Value
Text_LoopNo.Value = "116 Done"
Me.Repaint
DoEvents
strSql = "DELETE * FROM [122 Power Rankings DB]"
CurrentDb.Execute strSql, dbFailOnError
strSql = "INSERT INTO [122 Power Rankings DB] SELECT [122 Power
Rankings].* FROM [122 Power Rankings]"
CurrentDb.Execute strSql, dbFailOnError
txtTimeFinished.Value = Now
txtTotalTime.Value = txtTimeFinished.Value - txtTimeStart.Value
Text_LoopNo.Value = "122 Done"
Me.Repaint
DoEvents
Dim tdfNew As TableDef, rs As Recordset
Dim wrkDefault As Workspace
Dim dbsTemp As Database, strTempDatabase As String
Dim strTableName As String
Dim strTableName1 As String
Dim strTableName2 As String
' Get default Workspace.
Set wrkDefault = DBEngine.Workspaces(0)
strTempDatabase = Left$(CurrentDb.Name, Len(CurrentDb.Name) - 4) & "
temp.mdb"
' Make sure there isn't already a file with the name of
' the new database.
If Dir(strTempDatabase) <> "" Then Kill strTempDatabase
End If
'Create a new temp database
Set dbsTemp = wrkDefault.CreateDatabase(strTempDatabase, dbLangGeneral)
strTableName = "143 Quality Scores Union Query DB"
strTableName1 = "153 Quality Scores Union Query DB"
strTableName2 = "163 Quality Scores Union Query DB"
'strBracketedTableName = "[" & strTableName & "]"
' Delete the link to the temp table if it exists
If TableExists(strTableName) Then
CurrentDb.TableDefs.Delete strTableName
End If
If TableExists(strTableName1) Then
CurrentDb.TableDefs.Delete strTableName1
End If
If TableExists(strTableName2) Then
CurrentDb.TableDefs.Delete strTableName2
End If
' Create the temp table
Set tdfNew = dbsTemp.CreateTableDef(strTableName)
With tdfNew
.Fields.Append .CreateField("Week", dbInteger)
.Fields.Append .CreateField("Season", dbInteger)
.Fields.Append .CreateField("Home", dbText)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Score",
dbDouble)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Score
Win-Loss", dbDouble)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Log Score",
dbDouble)
dbsTemp.TableDefs.Append tdfNew
End With
Set tdfNew1 = dbsTemp.CreateTableDef(strTableName1)
With tdfNew1
.Fields.Append .CreateField("Week", dbInteger)
.Fields.Append .CreateField("Season", dbInteger)
.Fields.Append .CreateField("Home", dbText)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Score",
dbDouble)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Score
Win-Loss", dbDouble)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Log Score",
dbDouble)
dbsTemp.TableDefs.Append tdfNew1
End With
Set tdfNew2 = dbsTemp.CreateTableDef(strTableName2)
With tdfNew2
.Fields.Append .CreateField("Week", dbInteger)
.Fields.Append .CreateField("Season", dbInteger)
.Fields.Append .CreateField("Home", dbText)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Score",
dbDouble)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Score
Win-Loss", dbDouble)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Log Score",
dbDouble)
dbsTemp.TableDefs.Append tdfNew2
End With
dbsTemp.TableDefs.Refresh
Dim tdfLinked As TableDef
' Link to the Import tables in the temp MDB
Set tdfLinked = CurrentDb.CreateTableDef(strTableName)
tdfLinked.Connect = ";DATABASE=" & strTempDatabase
tdfLinked.SourceTableName = strTableName
CurrentDb.TableDefs.Append tdfLinked
CurrentDb.TableDefs.Refresh
RefreshDatabaseWindow
Set rs = CurrentDb.OpenRecordset(strTableName, dbOpenDynaset,
dbAppendOnly)