Data type mismatch in criteria expression

[BonnieG]

I'm feeling really confused! I have a piece of VB code working perfectly when I "hardcode" a value into my script, but I get the run-time error '3464' data type mismatch in criteria expression when I run it with a variable.

Here is my code:

Private Sub cmb_department_AfterUpdate()
new_department_id = Trim(Replace(Me.cmb_department, vbCrLf, ""))
MsgBox new_department_id
If Me.department_id = Null Then
Else
    If Me.department_id = "" Then
Else
    Dim MyDB As Database
    Dim MyRS As Recordset
    Dim SQL As String
    Set MyDB = CurrentDb()
    SQL = "SELECT name, department_manager, department_code, address, organisation, phone_number from dbo_departments WHERE department_id = [COLOR=red][B]'" & new_department_id & "'"
[/B][/COLOR]    Set MyRS = MyDB.OpenRecordset(SQL)
    Recordset_Empty = False
    If MyRS.BOF And MyRS.EOF Then
        Recordset_Empty = True
    End If
    
        If Recordset_Empty = True Then
    
        ' Department code does not exist
        
        MsgBox "This department code does not exist in the departments table. Please create it first."
        Me.department_id = Null
        
    Else
        Me.Department = MyRS.Fields(0)
        Me.Site = MyRS.Fields(3)
        Me.Manager = MyRS.Fields(1)
        Me.organisation = MyRS.Fields(4)
        Me.ext_no = MyRS.Fields(5)
    
    End If
    
    MyRS.Close
    
End If
End If
DoCmd.RunCommand acCmdSaveRecord
Forms![frm_new_user].Refresh
End Sub

I've highlighted the piece of code that is breaking everything. If I change that piece of code to a value (an actual department_id, e.g. the number 2) the code executes perfectly. I know that the value of new_department_id is 2 (or whatever) as I have a MsgBox telling me so.

This is sending me loopy... any ideas as to why it's not working with the variable?

 

[bob fitz]

I think it will work if you delete the single quotes:
WHERE department_id = " & new_department_id

 

[BonnieG]

Hmm, I'm sure I tried that... I'm not back in the office until Monday to test it but just to confirm, would this be the solution?

SQL = "SELECT name, department_manager, department_code, address, organisation, phone_number from dbo_departments WHERE department_id = " & new_department_id & ""

Thanks Bob

 

[bob fitz]

Not quite. I think it should be:

SQL = "SELECT name, department_manager, department_code, address, organisation, phone_number from dbo_departments WHERE department_id = " & new_department_id

 

[BonnieG]

Ahhh I think that explains where I was going wrong. Thanks, will give it a go on Monday!

 

[bob fitz]

We will wait for Monday morning with bated breath.

 

[BonnieG]

Thank you Bob. Problem solved!