0

Any ideas why I am getting run-time error 3464 (Data type mismatch in criteria expression)?

my sql code is:

MySql1 = "UPDATE Thaw_Tags SET [Pulled_item]='" & Me.Text162 & "'," _
& "[fordate]='" & Me.Text164 & "',[Midnight_Meat]='" & Me.Combo168.Column(1) & "',[Midnight_Meat_Required]='" & Me.Combo200 & "',[Midnightserving_size]='" & Me.Text227 & "'," _
& "[Midnighttotal_servings]='" & Me.Text170 & "',[Breakfast_Meat]='" & Me.Combo172.Column(1) & "',[Breakfast_Meat_Required]='" & Me.Combo202 & "',[Breakfastserving_size]='" & Me.Text230 & "'," _
& "[Breakfasttotal_servings]='" & Me.Text174 & "',[Lunch1_Meat]='" & Me.Combo176.Column(1) & "',[Lunch1_Meat_Required]='" & Me.Text212.Value & "'," _
& "[Lunch1serving_size]='" & Me.Text233 & "',[Lunch1total_servings]='" & Me.Text178.Value & "',[Lunch2_Meat]='" & Me.Combo180.Column(1) & "',[Lunch2_Meat_Required]='" & Me.Text216 & "'," _
& "[Lunch2serving_size]='" & Me.Text237 & "',[Lunch2total_servings]='" & Me.Text182 & "',[Lunch3_Meat]='" & Me.Combo184.Column(1) & "',[Lunch3_Meat_Required]='" & Me.Text220 & "'," _
& "[Lunch3serving_size]='" & Me.Text241 & "',[Lunch3total_servings]='" & Me.Text186 & "',[Dinner1_Meat]='" & Me.Combo188.Column(1) & "',[Dinner1_Meat_Required]='" & Me.Text214 & "',[Dinner1serving_size]='" & Me.Text235 & "'," _
& "[Dinner1total_servings]='" & Me.Text190 & "',[Dinner2_Meat]='" & Me.Combo192.Column(1) & "',[Dinner2_Meat_Required]='" & Me.Text218 & "', [Dinner2serving_size]='" & Me.Text239 & "',[Dinner2total_servings]='" & Me.Text194 & "'," _
& "[Dinner3_Meat]='" & Me.Combo196.Column(1) & "',[Dinner3_Meat_Required]='" & Me.Text222 & "',[Dinner3serving_size]='" & Me.Text243 & "',[Dinner3total_servings]='" & Me.Text198 & "'" _
& "WHERE [Day]='" & Me.Combo160 & "'"

Debug.Print MySql1
DoCmd.RunSQL MySql1

which on debug prints:

UPDATE Thaw_Tags SET [Pulled_item]='4/10/2018',[fordate]='4/13/2018',[Midnight_Meat]='Asian BBQ Turkey',[Midnight_Meat_Required]='TURKEY BREAST BONELESS',[Midnightserving_size]='1.25',[Midnighttotal_servings]='25',[Breakfast_Meat]='Asian Bourbon Beef & Vegetables over Noodles',[Breakfast_Meat_Required]='BEEF GROUND 90/10',[Breakfastserving_size]='0.625',[Breakfasttotal_servings]='30',[Lunch1_Meat]='Baked Chicken',[Lunch1_Meat_Required]='CHICKEN 1/4 CUT',[Lunch1serving_size]='1',[Lunch1total_servings]='35',[Lunch2_Meat]='Baked Turkey and Noodles',[Lunch2_Meat_Required]='Turkey Roll',[Lunch2serving_size]='1.15',[Lunch2total_servings]='45',[Lunch3_Meat]='Asian Bourbon Beef & Vegetables over Noodles',[Lunch3_Meat_Required]='BEEF GROUND 90/10',[Lunch3serving_size]='0.625',[Lunch3total_servings]='55',[Dinner1_Meat]='Baked Dijon Pork Chop',[Dinner1_Meat_Required]='PORK LOIN CHOPS',[Dinner1serving_size]='1.5625',[Dinner1total_servings]='40',[Dinner2_Meat]='Asian BBQ Turkey',[Dinner2_Meat_Required]='TURKEY BREAST BON
ELESS', [Dinner2serving_size]='1.25',[Dinner2total_servings]='50',[Dinner3_Meat]='Baked Chicken',[Dinner3_Meat_Required]='CHICKEN 1/4 CUT',[Dinner3serving_size]='1',[Dinner3total_servings]='60'WHERE [Day]='7'

from what I see in the debug everything looks good...

Erik A
  • 31,639
  • 12
  • 42
  • 67
user2002716
  • 120
  • 2
  • 11
  • That's a lot of string concatenation. I strongly recommend using parameters instead. If you want to know how, see [How do I use parameters in VBA?](https://stackoverflow.com/q/49509615/7296893) – Erik A Apr 08 '18 at 20:34
  • Thanks, i was able to find the problem. Using me.combo160 was screening everything up, using Form!FORM!Combo160 fixed it – user2002716 Apr 08 '18 at 21:05
  • Easily debugged by removing stuff until the error goes away. – nicomp Apr 08 '18 at 21:33

1 Answers1

1

Since you only have one criteria expression, that is it. 

WHERE [Day]='7'

should probably be

WHERE [Day]=7

Once you have solved that, you will have a ton of similar errors. There are lots of fields that sounds like numerical fields, but you have the values in single quotes.

Sam
  • 5,424
  • 1
  • 18
  • 33
  • Wouldn't that be a good reason to parameterize the query instead of concatenating the values and having to deal with this mess of single quotes? – Mathieu Guindon Apr 08 '18 at 19:45
  • And _date_ value should look: `UPDATE Thaw_Tags SET [Pulled_item]=#4/10/2018# ` etc. – Gustav Apr 08 '18 at 19:53