Changed from Number to Text, now I get "Data type mismatch" error

[note4shawn]

Hello all,
I have a form with a combobox that shows a list of employees from a query. Once an employee is chosen you can open either open a form to edit this employees data or you can open a report to view the data.

I had this working as expected but I had to change the employee number field (which is the field/column that is bound in the combobox) from Number to Text. Now I am getting the "Run-time error '3464': Data type mismatch in criteria expression" when I choose an employee. I have made sure that all the employee number fields that this field is linked to are also Text data types. All my queries work so this data type mismatch must have to do with the code. The following is the code that is attached to the combobox AfterUpdate().

Private Sub Combo24_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[emp_number] = " & Str(Nz(Me![Combo24], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

When I debug the red text is highlighted. I am not really fluent in coding. I know just enough to piece things together and make them work. I am not sure exactly what is wrong with this code other than it obviously must only be for Number data types.

Can someone tell me how I can fix this code to work with Text data types? Or am I completely wrong and it is something else?

 

[CJ_London]

if you are using text you need to surround with single quotes so this

rs.FindFirst "[emp_number] = " & Str(Nz(Me![Combo24], 0))


becomes

rs.FindFirst "[emp_number] = '" & Str(Nz(Me![Combo24], 0)) & "'"

not sure why you are converting your combo24 to string - so all you should need is

rs.FindFirst "[emp_number] = '" & Nz(Me![Combo24], 0) & "'"

 

[Mark_]

As important, why did you need to change [emp_number] to text? Anyone reading the field name will think this is numeric because it has "number" in it. If it really needs to be a string then you would want to rename the field to something more appropriate.

 

[note4shawn]

Thanks guys. The single quotes worked!

 

[sps2]

This really did work. Thank you. I know your not a teacher but can you explain why that worked. I mean I had this database set up with the employee number as a number field for many years. Switching to short text cause all that because why? Thank you for any enlightenment.

 

[CJ_London]

its because you treat text differently to numbers. Number don't use quotes, text does. Also be aware that text works differently to numbers with sorting

number sorted will give you
111
1020

text will give
1020
111

Edit: the rule applies through the whole of the programming world with the possible exception of excel which uses variant datatypes. Think about vba

myText="123"
myNum=123

 

[sps2]

Thank you CJ.