Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 16th, 2005, 11:28 PM
Registered User
 
Join Date: Mar 2005
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Data Type Mismatch in criteria expression

Thanks in advance to anyone to can help.

I am trying to modify the Word Automation exercise from 'Beginning Access 2000 VBA'. I am getting a runtime error at Set recAsset = db.OpenRecordset(strSQL)

The code is below. Any help would be greatly appreciated.

This is my first VB project and it's definately baptism by fire.

Option Compare Database


Public Const m_strDIR As String = "D:\Documents and Settings\213043653\Desktop\Remarketing Database\"
Public Const m_strTEMPLATE As String = "ReturnLetter.dot"

Public m_objWord As Word.Application
Public m_objDoc As Word.Document
Public Sub ReOrder()

Dim db As Database
Dim recLease As Recordset
Dim recAsset As Recordset
Dim strSQL As String
Dim strItems As String
Dim strSerialNBR As String
Dim strDescription As String


Set db = CurrentDb()

Set recLease = db.OpenRecordset("LetterLease")
Set recAsset = db.OpenRecordset("LetterAsset")

While Not recLease.EOF
    strSQL = "SELECT * FROM LetterAsset " & _
        " WHERE Lease_NBR = " & recAsset("Lease_NBR")
    Set recAsset = db.OpenRecordset(strSQL)
    strItems = "Serial_NBR" & vbTab & "Description"
    While Not recAsset.EOF
        strItems = strItems & vbCrLf & recAsset("Serial_Nbr") & _
            vbTab & recAsset("Description")
    Wend

CreateOrderLetter recLease, recAsset

recLease.Close
recAsset.MoveNext
Wend
recAsset.Close
Set recLease = Nothing
Set recAsset = Nothing


End Sub

Public Sub CreateOrderLetter(recLease, recAsset)

    Set m_objWord = New Word.Application
    Set m_objDoc = m_objWord.Documents.Add(m_strDIR & m_strTEMPLATE)


    ' insert the customer details
    InsertTextAtBookMark "Lease_Nbr", recLease("Lease_NBR")
    InsertTextAtBookMark "Customer_Name2", recLease("Customer_Name")
    InsertTextAtBookMark "Contact_Name", recLease("Contact_Name")
    InsertTextAtBookMark "Lease_Nbr2", recLease("Lease_NBR")
    InsertTextAtBookMark "Lease_Nbr3", recLease("Lease_NBR")
    InsertTextAtBookMark "Whs_Name", recLease("Whs_Name")
    InsertTextAtBookMark "Whs_Add_1", recLease("Whs_Add_1")
    InsertTextAtBookMark "Whs_City", recLease("Whs_City")
    InsertTextAtBookMark "Whs_State", recLease("Whs_State")
    InsertTextAtBookMark "Whs_Zip", recLease("Whs_Zip")
    InsertTextAtBookMark "Whs_Contact", recLease("Whs_Contact")
    InsertTextAtBookMark "Whs_Contact2", recLease("Whs_Contact")
    InsertTextAtBookMark "Whs_Contact_Nbr", recLease("Whs_Contact_Nbr")
    InsertTextAtBookMark "Whs_Contact_Fax", recLease("Whs_Contact_Fax")
    InsertTextAtBookMark "Whs_Contact_Fax2", recLease("Whs_Contact_Fax")
    InsertTextAtBookMark "Customer_Name", recLease("Customer_Name")


InsertItemsTable (recDetails)

m_objDoc.SaveAs FileName:=m_strDIR & recLease("Lease_Nbr") & _
" - " & FormatDateTime(Date, vbLongDate) & ".DOC"

m_objWord.Visible = True




End Sub

Private Sub InsertTextAtBookMark(strBkmk As String, varText As Variant)

    ' selects the bookmark and inserts the text
    m_objDoc.Bookmarks(strBkmk).Select
    m_objWord.Selection.Text = varText & ""

End Sub

Private Sub InsertItemsTable(recR As Recordset)

    Dim strTable As String
    Dim objTable As Word.Table

    strTable = "Serial NBR" & vbTab & "Description" & vbCr
    recR.MoveFirst
    While Not recR.EOF
        strTable = strTable & recR("Serial_Nbr") & vbTab & _
            recR("Description") & vbCr
        recR.MoveNext
    Wend

    InsertTextAtBookMark "Details", strTale
    Set objTable = m_objWord.Selection.ConvertToTable(Separator:=vbTa b)
    With objTable
        .AutoFormat Format:=wdTableFormatClassic3, AutoFit:=True, _
            ApplyShading:=False
    End With

    Set objTable = Nothing

End Sub
 
Old March 17th, 2005, 10:16 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

When you mouse over stSQL in the debugger, what is the value for stSQL? My guess is that it is something like this:

"SELECT * FROM LetterAsset WHERE Lease_NBR = X"

If the value passed from recAsset("Lease_NBR") is an integer, then this should work. If the value passed is a string, then you need to write stSQL like this:

"SELECT * FROM LetterAsset " & _
" WHERE Lease_NBR = " & "'" & recAsset("Lease_NBR") & "'"

You may want to try this anyway.

You may also need to add this to your error line:


HTH



mmcdonal
 
Old March 17th, 2005, 12:32 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Sorry, I was going to put in connection, cursor and locking info.

mmcdonal
 
Old March 17th, 2005, 01:25 PM
Registered User
 
Join Date: Mar 2005
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

mmcdonal.... thanks for the quick reply.
You are right on target where it(X) was oroginally looking for an integer and I'm now refering to a text syring. Your changes didn't have any effect although your solution sounded dead-on. Any other thoughts?
 
Old March 18th, 2005, 04:42 PM
Registered User
 
Join Date: Mar 2005
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I did it!

mmcdonal.....Thank you. You're correction was accurate and worked. I also learned that you haev to close the debugger after making corrections and run the routine again in order for your corrections to work. (Duh) This was my first real line of code for an application. I feel like a million bucks.





Similar Threads
Thread Thread Starter Forum Replies Last Post
data type mismatch in criteria expression coreyjustin Classic ASP Basics 1 December 10th, 2007 06:49 PM
Data type mismatch in criteria expression Tawanda BOOK: ASP.NET 2.0 Instant Results ISBN: 978-0-471-74951-6 0 May 5th, 2007 05:29 PM
Data type mismatch in criteria expression. imercha Classic ASP Basics 3 November 23rd, 2006 11:09 AM
Data type mismatch in criteria expression. EDEN Access ASP 1 November 22nd, 2006 01:19 AM
Data type mismatch in criteria expression. kalchev ASP.NET 2.0 Professional 5 April 11th, 2006 11:08 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.