How to reset or clear a date field 
Author Message
 How to reset or clear a date field

I have a MSAccess database that is created and maintained by my VB6
program. In one of the tables, there are a couple of "date" fields
(declared at the time of creation as type: dbDate). When a new record
is added to this table (by my VB6 program), these fields are ignored -
no date is put in them. Later on, various conditions in the program
update these fields with the current time and date.

I now have a situation where I want to clear this field of any data;
that is, I want it to be as it was before any time and date was stored
in it. So, my question is: what do I set this field to? Do I assign ""
to it? Or vbNull? Or what?

Thanks.



Tue, 31 Jan 2012 01:51:53 GMT  
 How to reset or clear a date field


Quote:
>I have a MSAccess database that is created and maintained by my VB6
> program. In one of the tables, there are a couple of "date" fields
> (declared at the time of creation as type: dbDate). When a new record
> is added to this table (by my VB6 program), these fields are ignored -
> no date is put in them. Later on, various conditions in the program
> update these fields with the current time and date.

> I now have a situation where I want to clear this field of any data;
> that is, I want it to be as it was before any time and date was stored
> in it. So, my question is: what do I set this field to? Do I assign ""
> to it? Or vbNull? Or what?

Update thetablename Set datefield1=null, datefield2=null


Tue, 31 Jan 2012 02:40:17 GMT  
 How to reset or clear a date field


Quote:



>>I have a MSAccess database that is created and maintained by my VB6
>> program. In one of the tables, there are a couple of "date" fields
>> (declared at the time of creation as type: dbDate). When a new record
>> is added to this table (by my VB6 program), these fields are ignored -
>> no date is put in them. Later on, various conditions in the program
>> update these fields with the current time and date.

>> I now have a situation where I want to clear this field of any data;
>> that is, I want it to be as it was before any time and date was stored
>> in it. So, my question is: what do I set this field to? Do I assign ""
>> to it? Or vbNull? Or what?

>Update thetablename Set datefield1=null, datefield2=null

Your example seems to be an SQL statement. I don't know if it makes
any difference but I'm not doing the updates with SQL. Here's what I'm
doing:

I have a recordset globally declared and opened. Then I execute
statements like these:

rsMainData.Edit
rsMainData.Fields("FieldA").Value = "ABCD"
rsMainData.Fields("FieldB").Value = 1234
rsMainData.Fields("DateField").Value = Now  
rsMainData.Update

So, to "clear" the DateField, would I set thus:
    rsMainData.Fields("DateField").Value = vbNull



Tue, 31 Jan 2012 03:21:24 GMT  
 How to reset or clear a date field


<cut>

Quote:
>>Update thetablename Set datefield1=null, datefield2=null

> Your example seems to be an SQL statement. I don't know if it makes
> any difference but I'm not doing the updates with SQL.

Why not;  it's MUCH faster

Quote:
> Here's what I'm
> doing:

> I have a recordset globally declared and opened. Then I execute
> statements like these:

> rsMainData.Edit
> rsMainData.Fields("FieldA").Value = "ABCD"
> rsMainData.Fields("FieldB").Value = 1234
> rsMainData.Fields("DateField").Value = Now
> rsMainData.Update

> So, to "clear" the DateField, would I set thus:
>    rsMainData.Fields("DateField").Value = vbNull

vbNull is a constant with the numeric value 1 so you do not want that


Tue, 31 Jan 2012 03:28:09 GMT  
 How to reset or clear a date field

Quote:
> So, to "clear" the DateField, would I set thus:
>    rsMainData.Fields("DateField").Value = vbNull

No. vbNull is an enum member that is used to identify the type of a Variant.
VB enums are Longs, and this one has a value of 1, so what you have
effectively done is

    rsMainData.Fields("DateField").Value = 1

which would set your date to 1899-12-31. What you want to do is

    rsMainData.Fields("DateField").Value = Null



Tue, 31 Jan 2012 03:37:06 GMT  
 How to reset or clear a date field

Quote:
>    rsMainData.Fields("DateField").Value = vbNull

As other suggested, vbNull = 1 and is used only with VarType() Function. The
correct one to use is "Null". See "VarType Function" and "Null keyword" in
MSDN.


Tue, 31 Jan 2012 03:47:15 GMT  
 How to reset or clear a date field


Quote:


>> So, to "clear" the DateField, would I set thus:
>>    rsMainData.Fields("DateField").Value = vbNull

>No. vbNull is an enum member that is used to identify the type of a Variant.
>VB enums are Longs, and this one has a value of 1, so what you have
>effectively done is

>    rsMainData.Fields("DateField").Value = 1

>which would set your date to 1899-12-31. What you want to do is

>    rsMainData.Fields("DateField").Value = Null

Thanks, Jeff.

Just for my own edification, when I add a new record and I don't
assign ANYTHING to "DateField", is it's value then: Null  ?



Tue, 31 Jan 2012 03:59:54 GMT  
 How to reset or clear a date field


Quote:




> >> So, to "clear" the DateField, would I set thus:
> >>    rsMainData.Fields("DateField").Value = vbNull

> >No. vbNull is an enum member that is used to identify the type of a
Variant.
> >VB enums are Longs, and this one has a value of 1, so what you have
> >effectively done is

> >    rsMainData.Fields("DateField").Value = 1

> >which would set your date to 1899-12-31. What you want to do is

> >    rsMainData.Fields("DateField").Value = Null

> Thanks, Jeff.

> Just for my own edification, when I add a new record and I don't
> assign ANYTHING to "DateField", is it's value then: Null  ?

It would depend if you have any additional restrains on that field - Allow
Nulls, no-nulls, default, ...


Tue, 31 Jan 2012 03:59:22 GMT  
 How to reset or clear a date field

Quote:
> Just for my own edification, when I add a new record and I don't
> assign ANYTHING to "DateField", is it's value then: Null  ?

It's 0 or Null based on how you set up that field. However, 0 is a valid
Date value that means time information, in this case 12:00 AM. A Date is a
double, with the integer part representing the days since December 30, 1899,
and the fraction part represents the time of day. 0 = 12 AM, 0.25 = 6 AM,
0.5 = 12 PM, and so on. See "Date data type" in MSDN.

If you are not allowing Nulls, than just check for "<> 0", and set the value
to 0 if you want to return it to the default value as if nothing was
assigned to the field. If you are allowing nulls, then use IsNull().



Tue, 31 Jan 2012 06:45:08 GMT  
 How to reset or clear a date field


Quote:
> However, 0 is a valid Date value that means time information, in this case
> 12:00 AM.

I think this should be clarified, lest it confuse newbies. A date value with
an integer portion of 0 is a COMPLETE date and time value, the date portion
being 1899-12-30. When VB encounters this type of value and converts it to a
string, in the absence of any specific date formatting, VB will FORMAT the
resulting output as time only. However, there IS still a date associated
with that value, as you mentioned below:

Quote:
> A Date is a double, with the integer part representing the days since
> December 30, 1899, and the fraction part represents the time of day. 0 =
> 12 AM, 0.25 = 6 AM, 0.5 = 12 PM, and so on. See "Date data type" in MSDN.

I just found your statement of "0 means time information" a bit misleading.
It's really just a handy (lazy?) way of displaying time without explicitly
specifying a format string, i.e., relying on default behavior. And we all
know what most folks in this group think about relying on default
behavior....


Tue, 31 Jan 2012 23:30:00 GMT  
 How to reset or clear a date field


Quote:
> I just found your statement of "0 means time information" a bit
> misleading. It's really just a handy (lazy?) way of displaying time
> without explicitly specifying a format string, i.e., relying on default
> behavior. And we all know what most folks in this group think about
> relying on default behavior....

Too right.

If there is any question of ambiguity, I can see no reason not to add a
Boolean field to indicate if there is a date or not, this would completely
avoid the mucking about with NULLs.

Dave O.



Tue, 31 Jan 2012 23:53:47 GMT  
 How to reset or clear a date field


Quote:



>> I just found your statement of "0 means time information" a bit
>> misleading. It's really just a handy (lazy?) way of displaying time
>> without explicitly specifying a format string, i.e., relying on default
>> behavior. And we all know what most folks in this group think about
>> relying on default behavior....

> Too right.

> If there is any question of ambiguity, I can see no reason not to add a
> Boolean field to indicate if there is a date or not, this would completely
> avoid the mucking about with NULLs.

Is it harder to use "If IsNull(datefield) Then" rather than "If
IsThereADate=True Then" ?

adding an extra field for something that can be handled easily with the use
of Null values seems to me like extra overhead for no reason.



Wed, 01 Feb 2012 00:57:53 GMT  
 
 [ 12 post ] 

 Relevant Pages 

1. How to reset a date field From VB !!!

2. Clearing a date field

3. How to clear Date field?

4. Help on clear a date field!

5. Clearing DATE type field through ODBC

6. how can I clear date field in access database

7. How to clear a date field

8. How to clear a date field

9. Clearing date field from a record in a MDB file

10. Clearing Date Field - Access DB

11. Clearing Date Field.

12. This has got to be an easy one - Clearing A Date Field

 

 
Powered by phpBB® Forum Software