SQL update time field with iif statement 1

[rccs1]

I have the following vba sql statement to update and table with fields in time format. Every time I run it, I get Run-time error 3464. Data type mismatch in criteria expression.

The code is -


strSQL = "Update TempSchedfunction set " & _
"[EndTime] = '" & IIf(IsNull(Me.EndTime), Null, Me.EndTime) & "'"



DoCmd.SetWarnings False
db.Execute strSQL, dbFailOnError

EndTime is in medium Time format
if the endtime field is null then I want to update TempSchedfunction table with a null value otherwise retreive the value of the EndTime field.
If I remove the IIF and Is null statement, and the EndTime field has a value, the update works find. But when I do the IIF to check see if the field is null or not, I get the message.

I must be missing something basic. I have spent days on this without success. Can anyone help me.

 

[Remou]

You are using a text delimiter ('), the date delimiter is a hash sign (#).

 

[TheAceMan1]

How are ya rccs1 . . .

Additionally for date/time fields, you can't return a [blue]Null Value[/blue] or [blue]Null String[/blue] between hash marks! To set proper try the following:

[blue]   Dim db As DAO.Database, SQL As String, SetVal
   
   Set db = CurrentDb
   
   If IsNull(Me!EndTime) Then
      SetVal = "Null"
   Else
      SetVal = "#" & Me!EndTime & "#"
   End If
   
   SQL = "UPDATE TempSchedfunction " & _
         "SET [EndTime] = " & SetVal & ";"
   
   Set db = Nothing[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[rccs1]

Thanks TheAceMan1. Fixed the job.

 

[TheAceMan1]

rccs1 . . .

Sorry for not including the [blue]Db.Execute[/blue]. I was depending on you filling in that line. Apparently you did!

For those following this thread, the full code becomes:

[blue]   Dim db As DAO.Database, SQL As String, SetVal
   
   Set db = CurrentDb
   
   If IsNull(Me!EndTime) Then
      SetVal = "Null"
   Else
      SetVal = "#" & Me!EndTime & "#"
   End If
   
   SQL = "UPDATE TempSchedfunction " & _
         "SET [EndTime] = " & SetVal & ";"
   db.Execute SQL, dbFailOnError
   
   Set db = Nothing[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886