Data Type Mismatch in SQL

[Phred]

Today 02:08 PM
Phred

I am getting a data type mismatch on my append query. I printed out the results of the query in the immediate window -- at bottom. The variables pick up correctly from a form and the query shows the correct info is being processed.

I can't seem to find the mismatch. My variables are Integers, the table number fields are Long Integer.

Dim intCatID As Integer, stCatName As String, intMealId As Integer, dtDateAssigned As Date, stMealName As String, stSQL As String,

My table fields match as to data type, I think.

stSQL = "INSERT INTO Tbl_ScheduledMeal" _
& "( CategoryID, DayAssigned, CategoryName, MealID, MealName )" _
& "VALUES (" & intCatID & ", #" & dtDateAssigned & "#,'" & stCatName & "'," & intMealId & ",'" & stMealName & "');"

Results of Immediate window show the data is correct. I must have a problem with my table but I can't see where the problem is.



See attached pic of table fields.

INSERT INTO Tbl_ScheduledMeal( CategoryID, DayAssigned, CategoryName, MealID, MealName )
VALUES (7, #1/27/2012#,'BOX LUNCH - PRESCHOOL',142,'Banana Muffin - Preschool');

[Stingaway]

Might try removing one data element at a time to see which one is actually causing the data type mismatch.

You could just edit the text you pasted from the immediate window and remove one at a time, that way you could at least tell where the error is...

[Phred]

Hey thanks that did it. I hadn't thought about running one variable at a time but it sure grabbed the problem. This immediate window stuff is really cool.

Thanks