Solved

Run-time Error '13': Type mismatch in Access 2007

Posted on 2014-09-15
9
730 Views
Last Modified: 2014-09-17
I have this code but it gives me an error in the sqlstr connection.

Here's my code:
Private Sub ExportToText_a_Click()
On Error GoTo Err_ExportToText_a_Click
Dim strpath As String
Dim sfilter As String
Dim sfile As String
Dim lsCurPath As String
Dim rs As New ADODB.Recordset
Dim rsWrk As New ADODB.Recordset
'Dim rsWrk As DAO.Recordset
Dim rsHeader As New ADODB.Recordset
Dim rsDetail As New ADODB.Recordset
Dim TempBatch As String
Dim tempdate As String
Dim tempExtension As String
Dim lsUserID As String
Dim lsCommentVal As String
Dim lsCurrComment As String
Dim lsTempComment As String
Dim defaultIfNull As String
Dim lsCurCompanyID As String
Dim tranType As String
Dim detailType As String
Dim headerType As String
Dim useTaxType As String
Dim S As String
Dim tempInvoice As String
Dim lsquantity As String
Dim taxString As String
Dim vBatch As Date
Dim lsCurInvoiceNo As String
Dim detailDesc As String
Dim lsVendorID As String

    lsUserID = "admin"
    lsCommentVal = "0"
    lsTempComment = "Import-Invoice No:"
    defaultIfNull = "1"
    lsCurCompanyID = "Canton"
    taxString = "000 NOT TAXABLE"
    tranType = "401"
    detailType = "D"
    headerType = "V"
    useTaxType = "X"
    lsquantity = "1"
    S = ";"
    detailDesc = ""
    sfile = "ImportInvoices_"


    If Len(strpath) <= 0 Then
       strpath = "\\network\project\development\"
    End If
   
    sfilter = "del" & Chr(0) & "*.*" & Chr(0)
   
    'open browse file box for input file
    lsCurPath = ahtCommonFileOpenSave(, strpath, sfilter, , , sfile, "Choose a Location for AP Invoice Export...", , False)
     
   
     If lsCurPath = "" Then
        MsgBox "The Export Invoices operation was canceled.", , " "
        GoTo Cancel_ExportToText_a_Click
    End If
   
    'set filename of export file
    lsCurPath = lsCurPath & ".del"
   
    'open text file
    Open lsCurPath For Output As #1
   
   
    strsql = "Select * from tblMainImport ORDER BY InvoiceNo, InvoiceDate"
    rsHeader.Open strsql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
   
    Do Until rsHeader.EOF
   
    lsVendorID = rsHeader.Fields("VendorID").Value
    lsCurInvoiceNo = rsHeader.Fields("InvoiceNo").Value
    If rsHeader.Fields("InvoiceTotal") < 0 Then
            tranType = "402"
        Else
            tranType = "401"
        End If
       
        'write to text file in import format
        Print #1, headerType; S; S; S; lsVendorID; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; lsVendorID; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; Trim(rsHeader!InvoiceDate); S; lsCurInvoiceNo; S; tranType; S; S; defaultIfNull; S; lsUserID; S; S; S; S; S; S; Format(rsHeader!InvoiceDueDate, "mm/dd/yyyy"); S; S; S; S; S; S; S; S
       
        lsCurrComment = lsTempComment & Trim(lsCurInvoiceNo)
        detailDesc = Trim(rsHeader!Comment)
       
     
        'open detail and write to .del file
        sqlstr = "Select detail.* from tblMainImport" _
            & " where InvoiceNo = " & lsCurInvoiceNo & "" & " And VendorID = " & lsVendorID & "" & " ORDER By InvoiceNo"
           
       
 >>>>>  rsDetail.Open , sqlstr, CurrentProject.Connection, adOpenDynamic, adLockPessimistic
       
        Print #1, detailType; S; S; lsCommentVal; S; detailDesc; S; Trim(rsHeader!InvoiceTotal); S; S; S; Trim(lsCurrComment); S; S; S; lsquantity; S; S; Trim(rsHeader!FullGLAcctNo); S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; S; taxString; S; S; S; S; S; S; S; S; S; S
           
         
        rsDetail.MoveNext
       Loop    
    rsDetail.Close
   
    MsgBox "The Import Invoice has been created.", vbOKOnly, "Export Complete!"
       
    Set rsWrk = Nothing
    Set rsDetail = Nothing
    Set rsHeader = Nothing

   
On Error Resume Next

Exit_ExportToText_a_Click:
    Close #1
    Exit Sub
   
Cancel_ExportToText_a_Click:
    GoTo Exit_ExportToText_a_Click
Err_ExportToText_a_Click:
 GoTo Cancel_ExportToText_a_Click
End Sub
This-is-my-error-starts.docx
0
Comment
Question by:Queennie L
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
9 Comments
 
LVL 34

Accepted Solution

by:
Norie earned 500 total points
ID: 40324206
Remove the comma after  rsDetail.Open.
 rsDetail.Open  sqlstr, CurrentProject.Connection, adOpenDynamic, adLockPessimistic

Open in new window

0
 
LVL 15

Expert Comment

by:ChloesDad
ID: 40324319
I also think that the sql build is wrong

sqlstr = "Select detail.* from tblMainImport" _
             & " where InvoiceNo = " & lsCurInvoiceNo & "" & " And VendorID = " & lsVendorID & "" & " ORDER By InvoiceNo"

should be

sqlstr = "Select detail.* from tblMainImport" _
             & " where InvoiceNo = " & lsCurInvoiceNo & " And VendorID = " & lsVendorID & " ORDER By InvoiceNo"
if you don't need quotes or

sqlstr = "Select detail.* from tblMainImport" _
             & " where InvoiceNo = '" & lsCurInvoiceNo & "'" & " And VendorID = '" & lsVendorID & "'" & " ORDER By InvoiceNo"
If you do need quotes. Note the use of ' rather than "
0
 
LVL 85
ID: 40325080
This isn't right:
 
sqlstr = "Select detail.* from tblMainImport" _
            & " where InvoiceNo = " & lsCurInvoiceNo & "" & " And VendorID = " & lsVendorID & "" & " ORDER By InvoiceNo"

You're asking for "detail.*", which essentially means "Get me everything from the table named detail" - and you're querying tblMainImport.

If you want the values from a FIELD name "detail" in tblmainImport, then you'd do this:

sqlstr = "Select detail from tblMainImport" _
            & " where InvoiceNo = " & lsCurInvoiceNo & "" & " And VendorID = " & lsVendorID & "" & " ORDER By InvoiceNo"

If you want everything for the records found based on your WHERE clause:

sqlstr = "Select * from tblMainImport" _
            & " where InvoiceNo = " & lsCurInvoiceNo & "" & " And VendorID = " & lsVendorID & "" & " ORDER By InvoiceNo"

If you want to include a TABLE named "detail" in your query, you'd have to somehow JOIN that table to tblmainImport. We don't know your fields, so it's impossible to provide instructions on that.
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 37

Expert Comment

by:PatHartman
ID: 40325672
I also don't see a Dim statement for - sqlstr  OR strSQL if that is it's name.
Make sure that Option Explicit is set for each module and as the default for the database when new modules are created.
0
 
LVL 34

Expert Comment

by:Norie
ID: 40325699
The SQL might not be correct and you should fix it, but the reason for the Type Mismatch error is that comma I mentioned.:)
0
 

Author Closing Comment

by:Queennie L
ID: 40326409
Thanks.
0
 

Author Comment

by:Queennie L
ID: 40326469
@imnorie:

Is there a way I can group the details by InvoiceNo?

My output based on my code is not grouping it by InvoiceNo.

Example:

Same Vendor and Same InvoiceNo should be group together based on InvoiceNo. My code is not grouping by same invoiceno.

Attached is the file I want to accomplish.
GroupbyDetailsPerHeader.txt
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 40326596
Since you have already awarded points, it would be better to start a new question thread.
0
 
LVL 85
ID: 40327562
And what was the fix for this issue? While the comma was the cause, did you fixup the SQL statement also?
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question