Data Type Conversion Error with Empty String

I would like to delete the value of a Number field (ExerciseID) for a selected group of records in code. In the following code the ExerciseID is Number field. I get the following error when run the code: "Run Time Error: Data Type Conversion Error."

Set db = CurrentDb
strSQL = "SELECT * FROM qryTemplateDetail WHERE (((qryTemplateDetail.TemplateID)=" & ID & "));"

Set rstDelete = db.OpenRecordset(strSQL)
If IsNull(Me.frmTemplateDetailSub!TemplateID) Then
    DisplayMessage ("There are no records to delete.")
    Exit Sub
Else
    Do While Not rstDelete.EOF
       With rstDelete
            .Edit
            !ExerciseID = ""
            .Update
       End With
    rstDelete.MoveNext
    Loop
End If

If I change the "" to Null, (ie. !ExerciseID = Null), during the next sequence of code I get the following error: "You tried to assign the Null variable that is not a Variant data type."

Is there a way around this? Could I change the Number data type to a Variant on the fly? Thanks.
skennellyAsked:
Who is Participating?
 
NorieConnect With a Mentor VBA ExpertCommented:
Why not just use an UPDATE query?

strSQL = "UPDATE qryTemplateDetail SET ExerciseID = Null WHERE TemplateID = " & ID & "

DoCmd.RunSQL strSQL
0
 
peter57rConnect With a Mentor Commented:
On the face of it, this looks like far too much work for such a simple objective.

I don't know, of course, what complexity may be hidden in the query qryTemplateDetail .
But all it looks like is a case of a simple update query..

Update sometable set ExerciseID = Null where templateid = ID

but also  I can't see where you are getting the value of ID from.

As for the error, we would need to know about the definition of ExerciseID to make sure it is not a required field and not a primary key
0
 
skennellyAuthor Commented:
I will try the update query and let you know. Thanks.
0
 
skennellyAuthor Commented:
Partial solution
0
 
NorieVBA ExpertCommented:
How is it partial?

Is there something missing or incorrect?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.