I'm returning a recordset using DAO that contains a date field. In some instances (like a date value is more than 1 year old), I want to null the field in the recordset.
I've tried !Date = vbNull, but all that does is enter 12/31/1899 in the field. I want it to be null. Any suggestions?
Re: Null a date field in a recordset
I think that your vbNull is effectively putting a zero in the cell which Excel will interpret as 1 Jan 1900 (or apparently 31 Dec 1899 in your case). Try Clearcontents instead. Something like:
Re: Null a date field in a recordset
I'm afraid I can't help with DAO terms. By any change is there a DAO syntax for something like the following VBA equivalent?
!Date = ""
Re: Null a date field in a recordset
How are you returning the recordset?
If you are using SQL perhaps you can do something to return null values in that.
Re: Null a date field in a recordset
Hello again Norie. You've bailed me out so many times. By returning the recordset, do you mean to get into the form, or returning it to the database?
Re: Null a date field in a recordset
I'm not rightly sure because I don't quite know what you are doing.
Can you post any code you are using?
Re: Null a date field in a recordset
'populate the form
Set db1 = DBEngine.OpenDatabase(dbDSD)
Set rs1 = db1.OpenRecordset("SELECT * " & _
"FROM [Vendors] " & _
"WHERE LastOrder < #" & Date - 365 & "#;", dbOpenDynaset)
With rs1
If Not .EOF Then
.MoveLast 'populate the recordset
.MoveFirst 'and fill in the form
txtVendor.Value = !Name
txtAddress.Value = !Address
txtCity.Value = !City
txtState.Value = !State
txtZipCode.Value = Left(!ZipCode, 5)
txtPhone.Value = Format(!Phone, "(000) 000-0000")
txtFax.Value = Format(!Fax, "(000) 000-0000")
ckbLastOrder.Value = True
ckbLastOrder.Caption = !LastOrder
txtVendorID.Caption = !VendorID
End If
End With
.
.
.
'if the user unchecks the checkbox then
'I want to null the field
Set rs1 = db1.OpenRecordset("SELECT * " & _
"FROM [Vendors] " & _
"WHERE VendorID = " & txtVendorID.Value & ";", dbOpenDynaset)
with rs1
.edit
!LastOrder = vbNull
.update
end with
rs1.close
db1.closeRe: Null a date field in a recordset
Moved to correct forum.
Don’t have an account yet? Register yourself now and be a part of our community!