How do you update a null date into an access table date field 
Author Message
 How do you update a null date into an access table date field

VB6 | ADO 2.6 | ACCESS DATABASE | "DATE" FIELD

I have a textbox that holds a date.

The textbox"datafield" is pointing back to a field in an Access database
table.  When adding or editing a record, the textbox pulls and puts the date
into the database field with no problem.

Sometimes the end user enters a date, saves the record and then returns to
the textbox and deletes it's contents with the anticipation that that date
will be removed and replaced with a null value.

Unfortunately, the date field in the database after updating acts like it
can't save textbox.text = "" into the datefield.  Instead it keeps the
previously entered date which is not what the end user wants.

I have searched the help files in ADO and MSDN and have not been able to
locate the answer on how to remove a date value from a field after one has
been entered into it.  I can go into Access and delete the field and it just
acts like it's no big deal.  What am I missing?

What's the magic word so that the empty "date" field will be updated as null
(empty)?

Anyone have this problem before?



Wed, 06 Jul 2005 10:07:33 GMT  
 How do you update a null date into an access table date field
Judging by your notes, I'm guessing your using a 'bound' textbox with
possibly a data control.  If so, use the 'BeforeUpdate' event of the
data control to check the contents of the textbox.
Quote:

>VB6 | ADO 2.6 | ACCESS DATABASE | "DATE" FIELD

>I have a textbox that holds a date.

>The textbox"datafield" is pointing back to a field in an Access database
>table.  When adding or editing a record, the textbox pulls and puts the date
>into the database field with no problem.

>Sometimes the end user enters a date, saves the record and then returns to
>the textbox and deletes it's contents with the anticipation that that date
>will be removed and replaced with a null value.

>Unfortunately, the date field in the database after updating acts like it
>can't save textbox.text = "" into the datefield.  Instead it keeps the
>previously entered date which is not what the end user wants.

>I have searched the help files in ADO and MSDN and have not been able to
>locate the answer on how to remove a date value from a field after one has
>been entered into it.  I can go into Access and delete the field and it just
>acts like it's no big deal.  What am I missing?

>What's the magic word so that the empty "date" field will be updated as null
>(empty)?

>Anyone have this problem before?



Wed, 06 Jul 2005 11:30:18 GMT  
 How do you update a null date into an access table date field
Kevin,

I'm using a coded ADODB.Recordset that is setup and maintained by code only.
I probably gave to much information in the previous post.  Let's say I do
check the contents of the textbox and it's = "".  What do I code into the
table field so that the value will no longer be a Date ("01/01/2003") but
rather a null or "blank" value?  That's all I need to know.  I have tried
'txtDate.text = null' but no luck. When the recordset updates I'm thinking
it considers it an error and passes over that portion of the update.

NEW DEVELOPMENT:

I remarked out the 'On Error Resume Next' in the beginning of the update
subroutine and during the update code ADO generated the Following Error:

        Run-Time Error '-2147217842(80040e4e):
        Operation was cancelled.

any ideas now?

Thanks for the reply,

IkeT

Quote:
> Judging by your notes, I'm guessing your using a 'bound' textbox with
> possibly a data control.  If so, use the 'BeforeUpdate' event of the
> data control to check the contents of the textbox.


> >VB6 | ADO 2.6 | ACCESS DATABASE | "DATE" FIELD

> >I have a textbox that holds a date.

> >The textbox"datafield" is pointing back to a field in an Access database
> >table.  When adding or editing a record, the textbox pulls and puts the
date
> >into the database field with no problem.

> >Sometimes the end user enters a date, saves the record and then returns
to
> >the textbox and deletes it's contents with the anticipation that that
date
> >will be removed and replaced with a null value.

> >Unfortunately, the date field in the database after updating acts like it
> >can't save textbox.text = "" into the datefield.  Instead it keeps the
> >previously entered date which is not what the end user wants.

> >I have searched the help files in ADO and MSDN and have not been able to
> >locate the answer on how to remove a date value from a field after one
has
> >been entered into it.  I can go into Access and delete the field and it
just
> >acts like it's no big deal.  What am I missing?

> >What's the magic word so that the empty "date" field will be updated as
null
> >(empty)?

> >Anyone have this problem before?



Wed, 06 Jul 2005 22:34:22 GMT  
 How do you update a null date into an access table date field
Ike,

If you are building an SQL statement to do your update, you might try
something like:

        If txtYourDate <> Empty Then
            strSQLYourDate = "#" & txtYourDate & "#"
        Else
            strSQLYourDate = "NULL"
        End If

Just do this prior to assembling your SQL statement and you'll end up with
either the date in the textbox, or null if the textbox is empty.

John Baker


Quote:
> Kevin,

> I'm using a coded ADODB.Recordset that is setup and maintained by code
only.
> I probably gave to much information in the previous post.  Let's say I do
> check the contents of the textbox and it's = "".  What do I code into the
> table field so that the value will no longer be a Date ("01/01/2003") but
> rather a null or "blank" value?  That's all I need to know.  I have tried
> 'txtDate.text = null' but no luck. When the recordset updates I'm thinking
> it considers it an error and passes over that portion of the update.

> NEW DEVELOPMENT:

> I remarked out the 'On Error Resume Next' in the beginning of the update
> subroutine and during the update code ADO generated the Following Error:

>         Run-Time Error '-2147217842(80040e4e):
>         Operation was cancelled.

> any ideas now?

> Thanks for the reply,

> IkeT


> > Judging by your notes, I'm guessing your using a 'bound' textbox with
> > possibly a data control.  If so, use the 'BeforeUpdate' event of the
> > data control to check the contents of the textbox.


> > >VB6 | ADO 2.6 | ACCESS DATABASE | "DATE" FIELD

> > >I have a textbox that holds a date.

> > >The textbox"datafield" is pointing back to a field in an Access
database
> > >table.  When adding or editing a record, the textbox pulls and puts the
> date
> > >into the database field with no problem.

> > >Sometimes the end user enters a date, saves the record and then returns
> to
> > >the textbox and deletes it's contents with the anticipation that that
> date
> > >will be removed and replaced with a null value.

> > >Unfortunately, the date field in the database after updating acts like
it
> > >can't save textbox.text = "" into the datefield.  Instead it keeps the
> > >previously entered date which is not what the end user wants.

> > >I have searched the help files in ADO and MSDN and have not been able
to
> > >locate the answer on how to remove a date value from a field after one
> has
> > >been entered into it.  I can go into Access and delete the field and it
> just
> > >acts like it's no big deal.  What am I missing?

> > >What's the magic word so that the empty "date" field will be updated as
> null
> > >(empty)?

> > >Anyone have this problem before?



Wed, 06 Jul 2005 23:31:09 GMT  
 How do you update a null date into an access table date field
Thanks for responding John, but I'm still not where I need to be.

I'm using:
         adoPrimaryRS.UpdateBatch adAffectAll

to update the table in the database and without On Error Resume Next I
recieve an error when the recordset tries to update the date field with the
contents of an empty txtDate.text.  There has to be something you can equate
the txtDate.text to so that the date field in the database becomes zero,
nothing, null or whatever.

I'm hoping someone can just fill in the following when you're trying to
empty a pre-existing field containing a date (ie "02/03/2003") already
stored in the table so that it will contain nothing,zero, null or "notta".

What do you do set your bound textbox.text equal to so that this can be
done?

    txtDate.text = "Insert Correct Answer Here"

Thanks again for the help.  We are hitting all around the answer.  If this
sounds really stupid, it probably is.  I just want to know what Microsoft
would do.


Quote:
> Ike,

> If you are building an SQL statement to do your update, you might try
> something like:

>         If txtYourDate <> Empty Then
>             strSQLYourDate = "#" & txtYourDate & "#"
>         Else
>             strSQLYourDate = "NULL"
>         End If

> Just do this prior to assembling your SQL statement and you'll end up with
> either the date in the textbox, or null if the textbox is empty.

> John Baker



> > Kevin,

> > I'm using a coded ADODB.Recordset that is setup and maintained by code
> only.
> > I probably gave to much information in the previous post.  Let's say I
do
> > check the contents of the textbox and it's = "".  What do I code into
the
> > table field so that the value will no longer be a Date ("01/01/2003")
but
> > rather a null or "blank" value?  That's all I need to know.  I have
tried
> > 'txtDate.text = null' but no luck. When the recordset updates I'm
thinking
> > it considers it an error and passes over that portion of the update.

> > NEW DEVELOPMENT:

> > I remarked out the 'On Error Resume Next' in the beginning of the update
> > subroutine and during the update code ADO generated the Following Error:

> >         Run-Time Error '-2147217842(80040e4e):
> >         Operation was cancelled.

> > any ideas now?

> > Thanks for the reply,

> > IkeT


> > > Judging by your notes, I'm guessing your using a 'bound' textbox with
> > > possibly a data control.  If so, use the 'BeforeUpdate' event of the
> > > data control to check the contents of the textbox.


> > > >VB6 | ADO 2.6 | ACCESS DATABASE | "DATE" FIELD

> > > >I have a textbox that holds a date.

> > > >The textbox"datafield" is pointing back to a field in an Access
> database
> > > >table.  When adding or editing a record, the textbox pulls and puts
the
> > date
> > > >into the database field with no problem.

> > > >Sometimes the end user enters a date, saves the record and then
returns
> > to
> > > >the textbox and deletes it's contents with the anticipation that that
> > date
> > > >will be removed and replaced with a null value.

> > > >Unfortunately, the date field in the database after updating acts
like
> it
> > > >can't save textbox.text = "" into the datefield.  Instead it keeps
the
> > > >previously entered date which is not what the end user wants.

> > > >I have searched the help files in ADO and MSDN and have not been able
> to
> > > >locate the answer on how to remove a date value from a field after
one
> > has
> > > >been entered into it.  I can go into Access and delete the field and
it
> > just
> > > >acts like it's no big deal.  What am I missing?

> > > >What's the magic word so that the empty "date" field will be updated
as
> > null
> > > >(empty)?

> > > >Anyone have this problem before?



Thu, 07 Jul 2005 02:26:41 GMT  
 How do you update a null date into an access table date field
ANSWER FOUND HERE...

http://support.microsoft.com/default.aspx?scid=KB;en-us;q286241

Thanks again to both Kevin and John for replying to the post.

Ike

----------------------------------------------------------------------------
----------


Quote:
> VB6 | ADO 2.6 | ACCESS DATABASE | "DATE" FIELD

> I have a textbox that holds a date.

> The textbox"datafield" is pointing back to a field in an Access database
> table.  When adding or editing a record, the textbox pulls and puts the
date
> into the database field with no problem.

> Sometimes the end user enters a date, saves the record and then returns to
> the textbox and deletes it's contents with the anticipation that that date
> will be removed and replaced with a null value.

> Unfortunately, the date field in the database after updating acts like it
> can't save textbox.text = "" into the datefield.  Instead it keeps the
> previously entered date which is not what the end user wants.

> I have searched the help files in ADO and MSDN and have not been able to
> locate the answer on how to remove a date value from a field after one has
> been entered into it.  I can go into Access and delete the field and it
just
> acts like it's no big deal.  What am I missing?

> What's the magic word so that the empty "date" field will be updated as
null
> (empty)?

> Anyone have this problem before?



Thu, 07 Jul 2005 04:52:16 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Null date values in Date/Time fields in Access DB

2. REQ: Updating Access date field with a Null

3. Crystal Reports 8.5 date range parameter field - record selection that includes null date range

4. Update a DATE-field to a NULL-value

5. Update of date field to null fails

6. Update Dates in Access to Null?

7. Null in Date Field (Access)

8. Zero-Length Strings vs. NULL, Access Date Fields

9. How do I store an empty date on a date field (MS Access)

10. vb6 date variables comparing with access date fields returns always empty recordset

11. Selecting Date Fields that fall between two dates in Access

12. ADO with Access Date/Time field not storing the time, just the date

 

 
Powered by phpBB® Forum Software