Results 1 to 2 of 2
  1. #1
    nigelbloomy is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2012

    Data type mismatch error

    I am getting a datatype mismatch error on the final line of this code. Can anyone see why?

    Sub Rollup()    Dim rsQueryResults, rsCalculate, rsRollInto, rsBOMTable As DAO.Recordset
        Dim stQuery, strSQL, stParent As String
        Dim iLevel, i As Integer
        Dim dbRollup As Double
        'Find the highest level number in BOM Table and put it into a variable called iLevel
        stQuery = "SELECT Max([BOM Table].Level) AS MaxOfLevel FROM [BOM Table];"
        Set rsQueryResults = CurrentDb.OpenRecordset(stQuery)
        iLevel = rsQueryResults!MaxofLevel
        Debug.Print "iLevel="; iLevel
        Set rsQueryResults = Nothing
        Set rsBOMTable = CurrentDb.OpenRecordset("BOM Table", dbOpenDynaset)
        'Filter the BOM Table to show only the parts at the highest level
        stQuery = "SELECT [BOM Table].Parent_Number, [BOM Table].Item_Number, " & _
                "[BOM Table].Level, [BOM Table].Quantity_Per, [BOM Table].Labor_Cost " & _
                "FROM [BOM Table] WHERE ((([BOM Table].Level)='" & iLevel & "'));"
        Set rsCalculate = CurrentDb.OpenRecordset(stQuery)

  2. #2
    nigelbloomy is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2012
    Fixed. For some reason the single quotes around " & iLevel & " in the WHERE section was messing up the query. I have had to use the single quotes before to get the queries to work, so I don't know why they messed up this query.

    Does someone know the rules about when to use single quotes and when not to?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Data type mismatch error
    By Tomfernandez1 in forum Programming
    Replies: 5
    Last Post: 10-05-2012, 07:27 AM
  2. Replies: 1
    Last Post: 05-11-2012, 10:59 AM
  3. Data type mismatch error
    By AccessUser123 in forum Queries
    Replies: 1
    Last Post: 05-22-2011, 07:48 PM
  4. Replies: 2
    Last Post: 05-17-2011, 02:40 PM
  5. Data type mismatch error on all of my queries!
    By MarkGLyons in forum Queries
    Replies: 3
    Last Post: 12-27-2010, 01:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums - Senior Forums