Update query: Data type mismatch

[David M C]

Here's my code:

Do While currentDate <= tempFinish
strSQL = "UPDATE Forecast SET Forecast.totalValue = ""totalValue""
+" & dailyValue & " WHERE (((Forecast.calDate)=#" & Format(currentDate,
"mm/dd/yyyy") & "#));"
cmd.CommandText = strSQL
Debug.Print strSQL
cmd.Execute
currentDate = currentDate + 1
Loop

And here's the strSQL output in the immediate window:

UPDATE Forecast SET Forecast.totalValue = "totalValue" +263.4528 WHERE
(((Forecast.calDate)=#01/01/2006#));

However, I get a "data type mismatch in criteria expression" error. I don't
get this error if I remove the #'s, but none of the fields get updated. Any
ideas?

Thanks,

Dave

 

[Duane Hookom]

Try this:

Do While currentDate <= tempFinish
strSQL = "UPDATE Forecast " & _
"SET [totalValue] = [totalValue] + " & dailyValue & _
" WHERE [calDate]=#" & Format(currentDate, "mm/dd/yyyy") & "#"
cmd.CommandText = strSQL
Debug.Print strSQL
cmd.Execute
currentDate = currentDate + 1
Loop

 

[David M C]

Thanks, sorted.

Try this:

Do While currentDate <= tempFinish
strSQL = "UPDATE Forecast " & _
"SET [totalValue] = [totalValue] + " & dailyValue & _
" WHERE [calDate]=#" & Format(currentDate, "mm/dd/yyyy") & "#"
cmd.CommandText = strSQL
Debug.Print strSQL
cmd.Execute
currentDate = currentDate + 1
Loop

--
Duane Hookom
MS Access MVP

Here's my code:

Do While currentDate <= tempFinish
strSQL = "UPDATE Forecast SET Forecast.totalValue = ""totalValue""
+" & dailyValue & " WHERE (((Forecast.calDate)=#" & Format(currentDate,
"mm/dd/yyyy") & "#));"
cmd.CommandText = strSQL
Debug.Print strSQL
cmd.Execute
currentDate = currentDate + 1
Loop

And here's the strSQL output in the immediate window:

UPDATE Forecast SET Forecast.totalValue = "totalValue" +263.4528 WHERE
(((Forecast.calDate)=#01/01/2006#));

However, I get a "data type mismatch in criteria expression" error. I
don't
get this error if I remove the #'s, but none of the fields get updated.
Any
ideas?

Thanks,

Dave