Solved

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

Posted on 2014-09-15
9
688 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
  • 2
  • 2
  • 2
  • +2
9 Comments
 
LVL 33

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 84
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
 
LVL 34

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 33

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 34

Expert Comment

by:PatHartman
ID: 40326596
Since you have already awarded points, it would be better to start a new question thread.
0
 
LVL 84
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now