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

Queennie L
Queennie L used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Analyst Assistant
Commented:
Remove the comma after  rsDetail.Open.
 rsDetail.Open  sqlstr, CurrentProject.Connection, adOpenDynamic, adLockPessimistic

Open in new window

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 "
Most Valuable Expert 2012
Top Expert 2014

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Distinguished Expert 2017

Commented:
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.
NorieAnalyst Assistant

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

Author

Commented:
Thanks.

Author

Commented:
@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
Distinguished Expert 2017

Commented:
Since you have already awarded points, it would be better to start a new question thread.
Most Valuable Expert 2012
Top Expert 2014

Commented:
And what was the fix for this issue? While the comma was the cause, did you fixup the SQL statement also?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial