data type mismatch in select query?

[swmorin]

I dont understand how I could be getting a data type mismatch in a select query.

Set misc_update = CurrentDb.OpenRecordset("SELECT tbl_instructor_dates.InstructorID, tbl_instructor_dates.InstructordateID, tbl_instructors.Instr_rank, tbl_instructors.Instr_lname, tbl_instructors.Instr_fname, tbl_instructor_dates.[281type], tbl_list_281_types.[281days], tbl_instructor_dates.Follow_up_due, tbl_instructor_dates.follow_up_req, tbl_instructor_dates.last281, tbl_instructor_dates.Next281, tbl_instructor_dates.FISDcw, tbl_instructor_dates.OandTcw, tbl_instructor_dates.TWcw, tbl_instructor_dates.FISDdue, tbl_instructor_dates.OandTdue, tbl_instructor_dates.TWdue, tbl_instructor_dates.REBICcw, tbl_instructor_dates.ISDcw, tbl_instructor_dates.TScw, tbl_list_281_types.[281ID], [tbl_instructors.instr_rank] & "" & [tbl_instructors.instr_lname] & "" & ", " & "" & [tbl_instructors.instr_fname] AS full_name " & _
" FROM (tbl_instructors INNER JOIN tbl_instructor_dates ON tbl_instructors.InstructorID=tbl_instructor_dates.InstructorID) INNER JOIN tbl_list_281_types ON tbl_instructor_dates.[281type]=tbl_list_281_types.[281ID] " & _
" WHERE (((tbl_instructor_dates.InstructorID)=[Forms]![frm_instructor_profile]![Combo13]));")
Set [Forms]![frm_instructor_misc_info_update]![281_type] = [misc_update]![281type]

 

[tehNellie]

It generally means you're trying to compare two fields of a different data type, Ie a text field to a number field.

Check your Join and WHERE clause columns to make sure you're comparing like for like.

 

[plog]

I would look at all the fields used in your JOIN. Most likely one is stored as a text and one is a number and when you try and make the connection you can't because their types are different.

 

[swmorin]

I checked all the fields and all the fields are bumbers, and the field in the where is a number and the combo box from the where statement is bound to a number field.

still saying data type mismatch

 

[swmorin]

ok if i put the sql into the query builder it works, but if I put the sql intoo the vba in th eon current event of the form i get the datatyoe mismatch error?

 

[tehNellie]

put your SQL into a string variable and debug.print it to make sure you're constructing it properly

i.e. you end up with "WHERE numericField = 1234" rather than "Where numeric field = me.txtbox.value"

having just looked at your SQL string, the latter is what is happening.

 

[spikepl]

Do as tehNellie suggested. Also read the second post in this thread: http://www.pcreview.co.uk/forums/problems-openrecordset-using-query-t1153311.html , by Allen Browne himself

Expression service ( the bit that tells the SQL what "[Forms]![frm_instructor_profile]![Combo13]" means), is not available in OPenRecordset, and this is the cause of your predicament.

 

[tehNellie]

The problem is the SQL string itself, the syntax and formatting on the first line around the instructor Full_name is wrong and the value from the combobox isn't formatted correctly.

 

[swmorin]

now it says data type conversion error.

Dim misc_update As Recordset
Set misc_update = CurrentDb.OpenRecordset("SELECT tbl_instructor_dates.InstructorID, tbl_instructor_dates.InstructordateID, tbl_instructors.Instr_rank, tbl_instructors.Instr_lname, tbl_instructors.Instr_fname, tbl_instructor_dates.[281type], tbl_list_281_types.[281days], tbl_instructor_dates.Follow_up_due, tbl_instructor_dates.follow_up_req, tbl_instructor_dates.last281, tbl_instructor_dates.Next281, tbl_instructor_dates.FISDcw, tbl_instructor_dates.OandTcw, tbl_instructor_dates.TWcw, tbl_instructor_dates.FISDdue, tbl_instructor_dates.OandTdue, tbl_instructor_dates.TWdue, tbl_instructor_dates.REBICcw, tbl_instructor_dates.ISDcw, tbl_instructor_dates.TScw, tbl_list_281_types.[281ID], [tbl_instructors.instr_rank] & "" & [tbl_instructors.instr_lname] & "" & ", " & "" & [tbl_instructors.instr_fname] AS full_name " & _
" FROM (tbl_instructors INNER JOIN tbl_instructor_dates ON tbl_instructors.InstructorID=tbl_instructor_dates.InstructorID) INNER JOIN tbl_list_281_types ON tbl_instructor_dates.[281type]=tbl_list_281_types.[281ID] " & _
" WHERE (((tbl_instructor_dates.InstructorID)= " & [Forms]![frm_instructor_profile]![Combo13] & "));")
Set [Forms]![frm_instructor_misc_info_update]![281_type] = [misc_update]![281type]

 

[MSAccessRookie]

now it says data type conversion error.

Dim misc_update As Recordset
Set misc_update = CurrentDb.OpenRecordset("SELECT tbl_instructor_dates.InstructorID, tbl_instructor_dates.InstructordateID, tbl_instructors.Instr_rank, tbl_instructors.Instr_lname, tbl_instructors.Instr_fname, tbl_instructor_dates.[281type], tbl_list_281_types.[281days], tbl_instructor_dates.Follow_up_due, tbl_instructor_dates.follow_up_req, tbl_instructor_dates.last281, tbl_instructor_dates.Next281, tbl_instructor_dates.FISDcw, tbl_instructor_dates.OandTcw, tbl_instructor_dates.TWcw, tbl_instructor_dates.FISDdue, tbl_instructor_dates.OandTdue, tbl_instructor_dates.TWdue, tbl_instructor_dates.REBICcw, tbl_instructor_dates.ISDcw, tbl_instructor_dates.TScw, tbl_list_281_types.[281ID], [tbl_instructors.instr_rank] & "" & [tbl_instructors.instr_lname] & "" & ", " & "" & [tbl_instructors.instr_fname] AS full_name " & _
" FROM (tbl_instructors INNER JOIN tbl_instructor_dates ON tbl_instructors.InstructorID=tbl_instructor_dates.InstructorID) INNER JOIN tbl_list_281_types ON tbl_instructor_dates.[281type]=tbl_list_281_types.[281ID] " & _
" WHERE (((tbl_instructor_dates.InstructorID)= " & [Forms]![frm_instructor_profile]![Combo13] & "));")
Set [Forms]![frm_instructor_misc_info_update]![281_type] = [misc_update]![281type]

Are all of the statements below true? If any of them is not, it could be the source of your problem.

• [tbl_instructors.instr_rank] is type STRING
• [tbl_instructors.instr_lname] is type STRING
• [tbl_instructors.instr_fname] is type STRING
• tbl_instructors.InstructorID is the same type as tbl_instructor_dates.InstructorID
• tbl_instructor_dates.[281type] is the same type as tbl_list_281_types.[281ID]
• tbl_instructor_dates.InstructorID is the same type as [Forms]![frm_instructor_profile]![Combo13]
• [Forms]![frm_instructor_misc_info_update]![281_type] is the same type as [misc_update]![281type]

 

[swmorin]

the last two items are comparing a combo box with a field in a table both fields are numbers and both combo boxes bound columns are number fields

 

[MSAccessRookie]

the last two items are comparing a combo box with a field in a table both fields are numbers and both combo boxes bound columns are number fields

What about the rest? The one that jumped out at me was whether or not [tbl_instructors.instr_rank] was type STRING

 

[swmorin]

Sorry for not specifying. The other statements you wrote is true. I still do not understand why it works in query builder and not in the .openrecordset

 

[MSAccessRookie]

Sorry for not specifying. The other statements you wrote is true. I still do not understand why it works in query builder and not in the .openrecordset

Access is much more forgiving regarding type conversions than VBA is. It is possible that Access will make a leap that VBA cannot take.

 

[swmorin]

I did not figure this out, but just mde three different recordsets to achieve what I wanted.