Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2012
    Posts
    28

    Unhappy Unanswered: HELP: Data type mismatch in Criteria Expression

    Hi. I know this has been discussed and I've tried the suggestions I have read as well as many other things but I can't get the following code to work. The code is in an event of the Chapter_No field of my subform. When the user changes the number I want the chapter_name field to update so that it matches other rows with the same chapter_no/chapter_name. There is something in the SQL statement that gives the error "Data type mismatch in Criteria Expression". It stops at Set rs = db.OpenRecordset(SQL).

    Chapter_Name was set to memo so I changed it to text and tested it but no luck. I've tried without distinct, as well as with just chapter_name and minus the rest of the SQL. Nothing seems to work. Can anybody see anything obvious in this code that would be causing the problem?

    Thanks heaps

    Code:
    Private Sub Chapter_No_AfterUpdate()
    Dim db As Database
    Dim rs As Recordset
    Dim SQL As String
    
    Me.Chapter_Name = Null
    
    Dim strSQL As String
            
    SQL = "Select distinct Chapter_Name from dbo_bm_Map where chapter_no = " & Me.Chapter_No & " And Product_ID = " & Me.PRODUCT_ID & " and Chapter_Name is not NULL"
           Set db = CurrentDb
           Set rs = db.OpenRecordset(SQL)
                
    Me.Chapter_Name = rs
    
    Me.Requery
    
    End Sub

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Are both [chapter_no] and [Product_ID] defined with a numeric data type into the table [dbo_bm_Map]?

    Further on, this:
    Code:
    Me.Chapter_Name = rs
    is incorrect and will be the next error you'll encounter.
    Have a nice day!

  3. #3
    Join Date
    Mar 2012
    Posts
    28
    Product_ID is a number. Chapter_No is text oddly. I must have done that for a reason but I can't remember why. I guess this should be a number.

    What should I do with Me.Chapter_Name = rs?

    Thanks

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If Chapter_No is defined as Test, the expression should be:
    Code:
    SQL = "Select distinct Chapter_Name from dbo_bm_Map where chapter_no = '" & Me.Chapter_No & "' And Product_ID = " & Me.PRODUCT_ID & " and Chapter_Name is not NULL"
    From:
    Code:
    Dim rs As Recordset
    rs obviously is a Recordset (probably DAO, but this depends on the version of Access you're using), while:
    Code:
    Me.Chapter_Name
    is a reference to the default property of a control (probably a TextBox or possibly a ComboBox, the complete reference being Me.Chapter_Name.Value).

    You cannot assign a Recordset object to the Value property of a TextBox or ComboBox). If, as I can understand, you want to assign the data retrieved from the query to the TextBox, you should use:
    Code:
    Me.Chapter_Name.Value = rs!Chapter_Name
    Also notice that if no record matches the WHERE part of the query, an empty RecordSet will be returned (BOF = True and EOF = True) which will cause an error to occur when you'll try to assign the contents of rs!Chapter_Name to the TextBox value (Run-time error '3021'), so you should test first:
    Code:
    If rs.BOF = False And rs.EOF = False Then
        Me.Chapter_Name.Value = rs!Chapter_Name
    End If
    You could achieve the same result more simply, using:
    Code:
    Dim strCriteria As String
    strCriteria = "chapter_no = '" & Me.Chapter_No & "' And Product_ID = " & Me.PRODUCT_ID & " and Chapter_Name is not NULL"
    Me.Chapter_Name.Value = DLookup("Chapter_Name", "dbo_bm_Map", strCriteria)
    Have a nice day!

  5. #5
    Join Date
    Mar 2012
    Posts
    28
    Thanks heaps. I ended up using your simple version and it worked perfectly. I thought there was a simple answer. I took into account your other points also.

    Thanks again.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •