Solved

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

Posted on 2014-09-15
9
720 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 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 36

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 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 36

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
default value of combobox 25 43
Runtime Error -2147467259 (80004005) 7 50
ORDER BY 7 39
Updating records selected in a multi select simple List box 29 28
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

730 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