In a Loop, how to Cast a Number (Integer) to String

[5tr0ud]

In a module, I'm trying to loop through all the records in a table, and change the number fields with 0 to an empty string (""). In the loop, I get a run-time error about a data conversion error, in trying to convert a Number to a String. The run-time error occurs where the code is yellow. Here's my code.

Also, if anyone has a better way to loop, I'll gladly try it. Thanks for your help.


Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("tblSSSCounseling", dbOpenDynaset)

If rs.RecordCount > 0 Then
rs.MoveFirst
Do Until rs.EOF
' do looping here
If (((((((((rs!CounselingAATime =0 Or rs!CounselingCATime = 0) Or rs!CounselingCJTime = 0) Or rs!CounselingCVTime = 0) Or rs!CounselingFATime = 0) Or rs!CounselingGSTime = 0) Or rs!CounselingPCTime = 0) Or rs!CounselingPMTime = 0) Or rs!CounselingWSTime = 0) Or rs!CounselingOTTime = 0) Then
rs.Edit
If rs!CounselingAATime = 0 Then rs!CounselingAATime = ""
If rs!CounselingCATime = 0 Then rs!CounselingCATime = ""
If rs!CounselingCJTime = 0 Then rs!CounselingCJTime = ""
If rs!CounselingCVTime = 0 Then rs!CounselingCVTime = ""
If rs!CounselingFATime = 0 Then rs!CounselingFATime = ""
If rs!CounselingGSTime = 0 Then rs!CounselingGSTime = ""
If rs!CounselingPCTime = 0 Then rs!CounselingPCTime = ""
If rs!CounselingPMTime = 0 Then rs!CounselingPMTime = ""
If rs!CounselingWSTime = 0 Then rs!CounselingWSTime = ""
If rs!CounselingOTTime = 0 Then rs!CounselingOTTime = ""

rs.Update

End If

rs.MoveNext
Loop
End If
rs.Close: Set rs = Nothing

End Sub

 

[cmmrfrds]

It is just as easy and more efficient to do in an update query. You cannot make a numeric field spaces or empty, but can make Null.

Update tblSSSCounseling
Set CounselingCATime =
iif(CounselingCATime=0,Null,CounselingCATime),
CounselingCJTime =
iif(CounselingCJTime=0,Null,CounselingCJTime),
etc......