error 3464 in Access 2010 database

[vba317]

I have converted an access 2003 database into an access 2010 database. I am getting a error 3464 Datatype mismatch. To create the new database I imported the existing modules,queries and forms from the access 2003 database into the access 2010 database. I have highlighted the error in Blue. Also When I open the database I have a docmd.Maximize command on the Form_Open the form does not open. Any help is appreciated. Tom

My references are:
Visual Basic editor
Microsoft Access 14.0 object library
OLE Automation
Microsoft ActiveX Data objects 2.8 Library
Microsoft DAO 3.6 Object Library
Microsoft ADO Ext 2.8 fro DDL and Security
Microsoft Excel 14.0 Object Library

module
Option Compare Database
Option Explicit


' ***********************************************
' *** GLOBAL VARIABLES FOR MANIPULATING EXCEL ***
' ***********************************************
Public goXL As Excel.Application ' The Excel Object variable
Public gbXLPresent As Boolean ' Identifies whether Excel is present upon starting
'Public Const gconSaveLocation As String = "\\salmfilesvr1\public\Client Services\AutoRpts\_RptSets\SUMMARY\SeniorMgmt\Rpts\" ' Path to save finished workbooks

Public Function XLCreate()

' ************************************************
' *** THIS SUB CREATES A NEW INSTANCE OF EXCEL ***
' ************************************************
On Error Resume Next
gbXLPresent = True
Set goXL = CreateObject("Excel.Application")
If goXL Is Nothing Then ' Check if Excel is installed
    gbXLPresent = False
Else
    goXL.Visible = True ' If there, make it visible
End If

End Function

Public Function XLKill()
' **************************************************
' *** THIS SUB CLOSES THE OPEN INSTANCE OF EXCEL ***
' **************************************************
If gbXLPresent = True Then
    goXL.Quit
    Set goXL = Nothing
    gbXLPresent = False
End If

End Function




Private Sub cmdXL_Click()

' *** DECLARE VARIABLES
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strWk As String
Dim strFileNm As String
Dim iRow As Integer

' *** Get Week and set File Path
strSQL = "SELECT Max(fil.dt_txt) AS Dt " & _
            "FROM dbo_SnrMan_Files fil " & _
                "INNER JOIN dbo_SnrMan_Data_DateOrder do ON fil.dt_dt = do.filedate " & _
            "WHERE (do.ord=1);"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
strWk = (rst![Dt])
strFileNm = "\\salmfilesvr1\Public\Client Services\AutoRpts\_RptSets\SUMMARY\SeniorMgmt\Rpts\" & (strWk) & "_SrMgmt.xls"
rst.Close
Set rst = Nothing
' Open Workbook
Call XLCreate ' OPEN EXCEL
If gbXLPresent = True Then
    With goXL
        .Workbooks.Open Filename:="\\salmfilesvr1\Public\Client Services\AutoRpts\_RptSets\SUMMARY\SeniorMgmt\SnrMan_Tmp.xlt" ' Open Template
        .Sheets("SnrManRpt").Select
    End With
    ' *** ADD TOTAL DATA TO WORKSHEET
    strSQL = "SELECT WS_Totals.* FROM WS_Totals ORDER BY Ord;"
 [Blue]   Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)  [/Blue]
    rst.MoveFirst
    Do Until rst.EOF
        iRow = (rst![Ord]) + 2
        With goXL.ActiveSheet
            .Cells(iRow, 1).Value = CStr(rst![FileDate])
            .Cells(iRow, 2).Value = (rst![1])
            .Cells(iRow, 3).Value = (rst![2])
            .Cells(iRow, 4).Value = (rst![3])
            .Cells(iRow, 5).Value = (rst![4])
            .Cells(iRow, 6).Value = (rst![5])
            .Cells(iRow, 7).Value = (rst![6])
            .Cells(iRow, 8).Value = (rst![7])
            .Cells(iRow, 9).Value = (rst![7b])
            .Cells(iRow, 10).Value = (rst![8])
            .Cells(iRow, 11).Value = (rst![9])
            .Cells(iRow, 12).Value = (rst![10])
            .Cells(iRow, 13).Value = (rst![11])
            .Cells(iRow, 14).Value = (rst![12])
            .Cells(iRow, 15).Value = (rst![13])
            .Cells(iRow, 16).Value = (rst![14])
            .Cells(iRow, 17).Value = (rst![15])
            .Cells(iRow, 18).Value = (rst![16])
            .Cells(iRow, 19).Value = (rst![17])
            .Cells(iRow, 20).Value = (rst![18])
            .Cells(iRow, 21).Value = (rst![19])
            .Cells(iRow, 22).Value = (rst![20])
            .Cells(iRow, 23).Value = (rst![21])
            .Cells(iRow, 24).Value = (rst![22])
            .Cells(iRow, 25).Value = (rst![23])
            .Cells(iRow, 26).Value = (rst![24])
            .Cells(iRow, 27).Value = (rst![25])
            .Cells(iRow, 28).Value = (rst![26])
            .Cells(iRow, 29).Value = (rst![27])
            .Cells(iRow, 30).Value = (rst![28])
            .Cells(iRow, 31).Value = (rst![29])
            .Cells(iRow, 32).Value = (rst![30])
            .Cells(iRow, 33).Value = (rst![31])
        End With
        rst.MoveNext
        iRow = iRow + 1
    Loop
    rst.Close
    Set rst = Nothing
    ' ADD CLIENT DETAIL DATA FOR CURRENT WEEK
    strSQL = "SELECT WS_CurMonUCI.* FROM WS_CurMonUCI ORDER BY UCI;"
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
    rst.MoveFirst
    iRow = 19
    Do Until rst.EOF
        With goXL.ActiveSheet
            .Cells(iRow, 1).Value = CStr(rst![UCI])
            .Cells(iRow, 2).Value = (rst![1])
            .Cells(iRow, 3).Value = (rst![2])
            .Cells(iRow, 4).Value = (rst![3])
            .Cells(iRow, 5).Value = (rst![4])
            .Cells(iRow, 6).Value = (rst![5])
            .Cells(iRow, 7).Value = (rst![6])
            .Cells(iRow, 8).Value = (rst![7])
            .Cells(iRow, 9).Value = (rst![7b])
            .Cells(iRow, 10).Value = (rst![8])
            .Cells(iRow, 11).Value = (rst![9])
            .Cells(iRow, 12).Value = (rst![10])
            .Cells(iRow, 13).Value = (rst![11])
            .Cells(iRow, 14).Value = (rst![12])
            .Cells(iRow, 15).Value = (rst![13])
            .Cells(iRow, 16).Value = (rst![14])
            .Cells(iRow, 17).Value = (rst![15])
            .Cells(iRow, 18).Value = (rst![16])
            .Cells(iRow, 19).Value = (rst![17])
            .Cells(iRow, 20).Value = (rst![18])
            .Cells(iRow, 21).Value = (rst![19])
            .Cells(iRow, 22).Value = (rst![20])
            .Cells(iRow, 23).Value = (rst![21])
            .Cells(iRow, 24).Value = (rst![22])
            .Cells(iRow, 25).Value = (rst![23])
            .Cells(iRow, 26).Value = (rst![24])
            .Cells(iRow, 27).Value = (rst![25])
            .Cells(iRow, 28).Value = (rst![26])
            .Cells(iRow, 29).Value = (rst![27])
            .Cells(iRow, 30).Value = (rst![28])
            .Cells(iRow, 31).Value = (rst![29])
            .Cells(iRow, 32).Value = (rst![30])
            .Cells(iRow, 33).Value = (rst![31])
            .Cells(iRow, 34).Value = (rst![32])
        End With
        rst.MoveNext
        iRow = iRow + 1
    Loop
    rst.Close
    Set rst = Nothing
    ' *** SAVE WORKBOOK
    goXL.ActiveWorkbook.SaveAs Filename:=strFileNm
    Call XLKill ' *** CLOSE EXCEL ***
    MsgBox "Report Created!", vbOKOnly, "WHOO!!!"
Else
    MsgBox "Can't create Excel Object", vbOKOnly, "Excel not found"
End If

End Sub

 

[dhookom]

I would attempt to remove the reference "Microsoft DAO 3.6 Object Library" since it is included in "Microsoft Access 14.0 object library"

Duane
Hook'D on Access
MS Access MVP

 

[vba317]

I removed the DAO 3.6 library and now i get a compile error on the Dim rst As DAO.Recordset.

 

[dhookom]

It's a bit surprising since you have basically the same code earlier in the sub. I would try comment out the first Set rst = Nothing.

Duane
Hook'D on Access
MS Access MVP

 

[vba317]

I added Set rst = Nothing right above the strSQL and I still get the same error. I am wondering if Access 2010 interprets date/time differently that access 2003. The reason why I am asking is because when i go into SQL and do a select
SELECT Max(fil.dt_txt) as Dt
FROM rptdata_xl.dbo.SnrMan_Files fil
I get 20140914

When I do the next select

SELECT filedate
FROM rptdata_xl.dbo.SnrMan_Data_DateOrder do
WHERE do.ord=1
I get 2014-09-14 00:00:00:0000
Does this make a difference?

 

[Andrzejek]

Since you are getting your data from Excel (is that right?), how is the data formatted in Excel: as Date, as Text, as Number?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[vba317]

No, in this case I am getting the data from an SQL 2005 database through linked tables. and I have checked the properties of the table and the fields in question:
Table Field Name DataType
dbo_SnrManFiles dt_txt Text
dbo_SnrManFiles dt_dt Date/Time
dbo_SnrMan_Data_DateOrder filedate Date/Time
dbo_SnrMan_Data_DateOrder ord Text

 

[dhookom]

Is WS_Totals a table or query?

Duane
Hook'D on Access
MS Access MVP

 

[vba317]

WS totals is a query.
I have made a mistake I am getting the error in the following part:

strSQL = "SELECT Max(fil.dt_txt) AS Dt " & _
            "FROM dbo_SnrMan_Files fil " & _
                "INNER JOIN dbo_SnrMan_Data_DateOrder do ON fil.dt_dt = do.filedate " & _
            "WHERE (do.ord=1);"
[blue] Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset) [blue]

 

[dhookom]

Your SQL assumes do.ord is numeric when it is actually text. Try:

"WHERE (do.ord=""1"");"

Duane
Hook'D on Access
MS Access MVP