Deleting values in date/time datatype fields

[grenee]

Good Day All.

Can anyone guide me how to make a date/time data field empty.

I have tried databaseNumber(p) = ""

also isnull, Null, Vbnull and empty.

None of these values work.

 

[pbaldy]

Setting to Null should work, unless the field is required. Showing the actual code might help.

 

[Minty]

In a query or VBA you would use Null.
So if you had a control on a form called txtReportDate in vba code you would use
Me.txtReportDate = Null

This doesn't make any sense databaseNumber(p) = ""

 

[grenee]

Thanks for your response.

Actually I used to temporally change the datatype to Text, then set the field to "", then change back to date/time datatype. This is reflected in my code.

 ' This procedure will clear all the cells in the InstructorAllocations table
 
Private Sub Command0_Click()
Dim curDatabase As Database
     
    Set curDatabase = CurrentDb
       
    Dim rstInstructorsAllocations As Object
    Dim F As Integer
    Dim t As Integer
    Dim p As Integer
    F = 8   'Field number or position. 0 is the 1st position
    p = 1   'Field name in "InstructorsAllocations" table
    
    
  ' Because the fields with data type Date/time do not accept the null value "", the following will
  ' Convert columns with type Date/time to text so that it can accept the null value "".
  ' On completion the fields must be converted back to the original Date/time data type.
  
  'curDatabase.Execute ("ALTER TABLE UniqueCoursesUnderClasses ALTER COLUMN " & p & " TEXT") 'Change data type to text
   
  Set rstInstructorsAllocations = curDatabase.OpenRecordset("UniqueCoursesUnderClasses")
  
          
t = rstInstructorsAllocations.RecordCount
Dim j, i As Integer
 
     For j = 1 To 15
         For i = 1 To t
    
                rstInstructorsAllocations.Edit
                
                rstInstructorsAllocations.Fields(F) = vbNull   ' Set fields to null
                
                 rstInstructorsAllocations.Update
                rstInstructorsAllocations.MoveNext
            
         Next i
        
              ' Change data type back to date type
                     Set rstInstructorsAllocations = Nothing
                   '  curDatabase.Execute ("ALTER TABLE UniqueCoursesUnderClasses ALTER COLUMN " & p & " DATETIME")
                     F = F + 1
                     p = p + 1
                   '  curDatabase.Execute ("ALTER TABLE UniqueCoursesUnderClasses ALTER COLUMN " & p & " TEXT")
                
                     Set rstInstructorsAllocations = curDatabase.OpenRecordset("UniqueCoursesUnderClasses")
                   
    Next j
     Set curDatabase = Nothing
     
     MsgBox "Successfully completed"
     
     
End Sub

 

[MarkK]

Do this in the immediate window . . .

? isnull(vbNull)

vbNull is not the same as Null. To set a field to Null, do . . .

rst.fields(1).value = Null

No, don't change the data type of the column in order to change a field's value.

 

[grenee]

Good Day all.

I wish to beg ever one who offered me assistance over the last 2 days and who I agonize relentlessly with my problem.

Please note that your solutions were correct form the start; however I had a programing oversight, which is worth explaining:

I discovered that my code was reading only the first record and updating that one, but I did not put data in that record to be updated, so it appeared like nothing was happening. It just did not read past the first to the other records with the data.

The general problem is that I should have used the moveLast and MoveFirst properties in order for all the records to be counted.

Thank you all so much