Make date field become blank

[SidCharming]

I have...
Dim rsCase As New ADODB.Recordset
Dim dFollowUpDate as Date

strSql="SELECT Compliance_SiteCodeUS, Compliance_RecordNo, Compliance_FU6MFUDate FROM tblCompliance"

rsCase.Open strSql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
Do Until rsCase.EOF

dFollowUpDate = vbNull
rsCase("Compliance_FU6MFUDate") = dFollowUpDate
rsCase.Update
Loop

I want tblCase to be updated with a Null value for the date above, but it does not over write the current value that is already there with a null.

How can I make the value for Compliance_FU6MFUDate become NULL?

I tried:
rsCase("Compliance_FU6MFUDate") = Null
rsCase("Compliance_FU6MFUDate") is null
rsCase("Compliance_FU6MFUDate") = ""

but I end up with the value not being changed in the table...




Sid from Minnesota

 

[Endeavor]

Is the follow up date nullable in the table definition?

 

[SidCharming]

It is nullable in the table. I went in and manually deleted the value in a field with no problem.

By default the value is set to 12/31/1899 to identify a known bad value. I want to remove it when identified as a definate blank field.



Sid from Minnesota

 

[SidCharming]

I think I am on to something... You can not set a date field to a <blank> or NULL. I am finding that when set to Null or vbnull it always gives a value of '12/31/1899'.

Any way to get around this?



Sid from Minnesota

 

[RoyVidar]

You can set a date field to null, as long as it's required property is set to No.

[tt]rsCase("Compliance_FU6MFUDate") = Null
rsCase.Update[/tt]

Should work, as should:
[tt]
strSql="update tblCompliance set Compliance_FU6MFUDate = Null"
currentproject.connection.execute strsql[/tt]

Roy-Vidar

 

[SidCharming]

Same result as before... the value becomes '12/31/1899' when I use the

rsCase("Compliance_FU6MFUDate") = Null
rsCase.Update


My guess is I need to capture the 12/31/1899 date and manipulate it in my records. Funny though empty going into the code and a date has to come out of it no matter what.



Sid from Minnesota

 

[formerTexan]

Hi

I came across this thread while attempting to work out the same problem (null values in a date/time field).

So am curious if there have been any further thoughts on the matter. So far, my only idea has been to maintain the field as a string type and then convert (CDate)if and when needed. Not a nice solution really.

Cheers,
Bill

 

[RoyVidar]

In the immediate pane (ctrl+g), try:

[tt]? format(0,"mm/dd/yyyy")[/tt]

It means that for some reason, 0 is stored in the field in stead of Null. I'm not able to replicate the problem.

One thought - if you use a datevariable where you don't assign any value, it is "instantiated" as 0, since only variables of the datatype variant can hold Nulls.

Roy-Vidar

 

[formerTexan]

Hello Roy (and any other readers),

First in answer to the last post:
? format(0,"mm/dd/yyyy") in the immediate window returns 12/30/1899

Here is an outline of the phenomena for any interested future readers.

Going to a table and setting a date field’s default value to 0 results in a value of 12:00:00 AM, not 0.
So date fields store zeros as 12:00:00 AM. However any date formatting, whether at the field, control or via code results in 12/30/1899 or the appropriately formatted variation.

Entering 12:00:00 AM into a date field and then formatting it not unexpectedly gives you 12/30/1899. What did surprise me was that any other similiar time values (such as 11:19:43 PM in a date formatted field will also produce a 12/30/1899 result.

Conversely, entering 12/30/1899 into a date/time field will leave you with 12:00:00 AM.

So I now see three approaches if you are at all worried about this sort of thing:

1. Very carefully validate data input to avoid the problemand hope your user doesn't have a legitimate need for 12:00:00 AM or 12/30/1899 values. I suppose Microsoft has some rationale for why they couldn't have used a date in the 3rd century;
2. Trap for 12:00:00 AM or 12/30/1899 values when the data is called up;
3. Use a text field and go through the aggravation of data type conversions.

Cheers,
Bill

 

[SkipVought]

MS Systems reference Dates to 1/1/1900 (DateSerial = 1)

ZERO is one day LESS than 1/1/1900. Has NOTHING to do with "12:00:00 AM" with is the Time component of Date/Time -- Time is FRACTIONS of a DAY. So 1.0 or 0.0 or (like today) 38413.412890162 with can be FORMATTED as 3/2/2005 9:54 AM and if it were just 38413, the same format would display 3/2/2005 12:00 AM


Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[RoyVidar]

Then try

[tt]? format(0,"hh:nn:ss AMPM")[/tt]

or for that sake, open a recordset on the table and convert to numeric

[tt]debug.print format(rs.fields("yourdate").value, "0.00")[/tt]

or

[tt]? format(#12:00:00 AM#, "0.00")[/tt]

Getting a value out of a Date/Time field either in date format or time format means there is a value in the field (0?), not Null which denotes an absence of value.

In the Microsoft products dates are numbers, yes numbers, they are just formatted as dates. In Access 31/12/1899 is day one (1), 30/12/1899 is day zero (0). 0 formatted as Time in the US style is "12:00:00 AM" and 0 formatted as Date is 30/12/1899.

So in answer to the questions
1 - store Null if you mean to store an absence of value, store whatever date you need when you need to store a date. If you are not able to store Null, then there is something wrong with either the table settings, the code or something else
2 - they are the same, only date vs time format and, in this context, denotes 0 as a value in the date/time field (not Null)
3 - NO, NO, NO - if you want dates, and need dates, store dates, never do any string thingies - it's gonna come back and bite you...

Roy-Vidar