vba - Data Type Mismatch in criteria expression Access 2010 with SQL Insert Statement -

vba - Data Type Mismatch in criteria expression Access 2010 with SQL Insert Statement -

i trying update linked table in access , getting error of info mismatch type in criteria expression. database fields float ones start hours.

i tried making hours field integer value, got different type mismatch message.

i new vb , access, dumb. appreciate help. here code.

private sub command30_click() dim monthformat string dim yearformat string dim fullyear string dim dateperformed string dim currdate string dim timeentered string dim empnum string dim acct string dim cat string dim cmnt string dim firstname string dim lastname string dim shift string dim addvacation string dim hours string if isnumeric(me.text12.value) hours = cint(me.text12.value) else: hours = me.text12.value monthformat = format(me.text10.value, "mm") yearformat = format(me.text10.value, "yy") fullyear = format(me.text10.value, "yyyy") dateperformed = format(me.text10.value, "yyyymmdd") currdate = format(datetime.date, "yyyymmdd") timeentered = format(datetime.time, "hhmmss") empnum = " & me.combo20.column(0) & " acct = " & me.combo20.column(1) & " cat = " & me.combo20.column(3) & " cmnt = " & me.combo20.column(4) & " firstname = " & me.combo20.column(5) & " lastname = " & me.combo20.column(6) & " shift = " & me.combo20.column(7) & " hours = " & me.text12.value & " addvacation = "insert dbo_r_pphrtrx" & _ "(date_performed, employee_number " & _ ", job_number " & _ ", release " & _ ", account, account_cr, batch_item, batch_number, burden_dollars, category, date_time_begun, date_time_complt, eff_var_dollars, employee_id, eo_flag, first_name " & _ ", hours_earned, hours_worked, hours_worked_set, labor_dollars, last_name, location_code, period_yyyymm, product_line, qty_complete, rate_var_dollars " & _ ", release_wo " & _ ", shift, [status], [time], work_center, date_entered, divisionid, comment " & _ ", late_charge, operation, overtime, project_task, reference, task_number, type_transaction, datacapserialnumber, cost_account, cs_period, work_order) " & _ " " & _ " values('" & dateperformed & "' , '" & empnum & "' " & _ ", switch(('" & monthformat & "' >= 1 , '" & monthformat & "' <= 3), ('01vh' + '" & yearformat & "'), ('" & monthformat & "' >= 4 , '" & monthformat & "' <= 6), ('02vh' + '" & yearformat & "'), ('" & monthformat & "' >= 7 , '" & monthformat & "' <= 9), ('03vh' + '" & yearformat & "'), ('" & monthformat & "' >= 10 , '" & monthformat & "' <= 12), ('04vh' + '" & yearformat & "'))" & _ ", switch(('" & monthformat & "' >= 1 , '" & monthformat & "' <= 3), ('01vh' + '" & yearformat & "'), ('" & monthformat & "' >= 4 , '" & monthformat & "' <= 6), ('02vh' + '" & yearformat & "'), ('" & monthformat & "' >= 7 , '" & monthformat & "' <= 9), ('03vh' + '" & yearformat & "'), ('" & monthformat & "' >= 10 , '" & monthformat & "' <= 12), ('04vh' + '" & yearformat & "'))" & _ ", '" & acct & "', '2500-x', '0', '0', '0', 'labor hrs', '" & dateperformed & "' + '0600', '" & dateperformed & "' + '0600', '0', 'accss', '', '" & firstname & "' " & _ ", '" & hours & "', '" & hours & "', '0', '0', '" & lastname & "', '01', '" & fullyear & "' + '" & monthformat & "', '01', '0', '0' " & _ ", switch(('" & monthformat & "' >= 1 , '" & monthformat & "' <= 3), ('01vh' + '" & yearformat & "'), ('" & monthformat & "' >= 4 , '" & monthformat & "' <= 6), ('02vh' + '" & yearformat & "'), ('" & monthformat & "' >= 7 , '" & monthformat & "' <= 9), ('03vh' + '" & yearformat & "'), ('" & monthformat & "' >= 10 , '" & monthformat & "' <= 12), ('04vh' + '" & yearformat & "'))" & _ ", '" & shift & "', '', '" & timeentered & "', '92', '" & currdate & "', 'jobscope', 'v' " & _ ", '', '', '', '', '', '', '', '', '', '', '')" docmd.runsql (addvacation) end sub

instead of "gluing together" long, ugly, , potentially troublesome insert statement might consider using recordset object insert new row:

class="lang-vbs prettyprint-override">dim cdb dao.database, rst dao.recordset set cdb = currentdb set rst = cdb.openrecordset("select * dbo_r_pphrtrx false", dbopendynaset) rst.addnew rst!date_performed = dateperformed rst!employee_number = empnum ' ... , on rest of fields rst.update rst.close set rst = nil set cdb = nil

sql vba ms-access access-vba