HOW TO INSERT DATE AUTOMATICALLY

[sales@discussions.microsoft.com tx sales]

I have created a database to use for my invoicing. The date field always
changes on past records to the current date. How can I set it to hold the
date on each record that it was created on and not the current date?

[John Vinson]

You need a Date/Time field - InvoiceDate let's say - in the Table. A
textbox on a Form is NOT the same thing! Set the table field's Default
property to Date(); it will populate with the date at the moment the
record is created, and retain that value.

John W. Vinson[MVP]

[pere...@jetemail.net]

John Vinson wrote:
> On Sun, 17 Jul 2005 21:18:11 -0700, "tx sales" <tx
> sa...@discussions.microsoft.com> wrote:
>
> >I have created a database to use for my invoicing. The date field always
> >changes on past records to the current date.
>

> You need a Date/Time field - InvoiceDate let's say - in the Table. A
> textbox on a Form is NOT the same thing! Set the table field's Default
> property to Date(); it will populate with the date at the moment the
> record is created, and retain that value.

Is not guaranteed to retain the value:

CREATE TABLE Test1
(key_col INTEGER NOT NULL,
created_date DATETIME DEFAULT DATE() NOT NULL);

INSERT INTO Table1 (key_col) VALUES (1);

UPDATE Test1
SET created_date = #1987-01-01#
WHERE key_col = 1;

Two procedures are required. One to create the row using the current
(created) date. Another to update the data but not the created date.
Grant privileges to the procs. Revoke permissions from the base tables.

[tx sales]

The 2 responses below did not help the date continues to change each day.

[tx sales]

The 2 responses below did not help the date continues to change each day.

[Duane Hookom]

Please reply with properties from your control on your form that is used for
entering new records:

Name:
Control Source:
Default Value:

Also the properties from the field in the table:

Name:
Default Value:

--
Duane Hookom
MS Access MVP

"tx sales" <txs...@discussions.microsoft.com> wrote in message
news:79408475-D343-4041...@microsoft.com...

[BruceM]

You have not demonstrated the need for more than what John has suggested.
The default value will not change once it is part of a record unless the user
deliberately does so. This is in contrast to setting the control source to
=Date() or some similar approach. If you believe I am in error, please
describe the circumstances under which a default value will change after the
record has been created.

[tx sales]

in the form section the requested info as follows:
Name: Invoice Date
Control Source:-Date()
Default Value:Date()

In the table section

Name: Invocie Date
Default Value: Date()

Thanks for any help you can give

[pere...@jetemail.net]

BruceM wrote:

> > > Set the table field's Default
> > > property to Date(); it will populate with the date at the moment the
> > > record is created, and retain that value.
> >
> > Is not guaranteed to retain the value:

> > Two procedures are required. One to create the row using the current
> > (created) date. Another to update the data but not the created date.
> > Grant privileges to the procs. Revoke permissions from the base tables.
>

> You have not demonstrated the need for more than what John has suggested.

I read "How can I set it to hold the date". A DEFAULT alone will not
achieve this.

[Duane Hookom]

Set the Control Source to the field, not the expression:
Control Source: [Invoice Date]

--
Duane Hookom
MS Access MVP

--

"tx sales" <txs...@discussions.microsoft.com> wrote in message

news:10AAC930-2DC6-496B...@microsoft.com...

[John Vinson]

On 18 Jul 2005 08:31:44 -0700, pere...@jetemail.net wrote:

>I read "How can I set it to hold the date". A DEFAULT alone will not
>achieve this.

A Default on the table field, as I suggested, will in fact work
perfectly well.

When a new record is created, it will store the system clock date into
that table field, at the moment the record is first "dirtied".

That value will not change thereafter.

You're mistaken, I fear!

John W. Vinson[MVP]

[tx sales]

Thanks John, I followed your instructions exactly and it keeps changing the
date to the current date, it is not holding the date I created the record.
All records have today's date.

[tx sales]

Sorry, I tried it as this =[Invoice Date] and it shows "#error" in the field
and can not be changed. Then I tried this [Invoice Date] and it leaves the
field blank and can not enter anything to it.

[pere...@jetemail.net]

John Vinson wrote:
> A Default on the table field, as I suggested, will in fact work
> perfectly well.
>
> When a new record is created, it will store the system clock date into
> that table field, at the moment the record is first "dirtied".
>
> That value will not change thereafter.
>
> You're mistaken, I fear!

An I think *you* are mistaken!

The DEFAULT is only applied when the row is first created (INSERT INTO)
and then only when no value was supplied. It don't stop users from
changing it (UPDATE). Try this

CREATE TABLE Test1
(key_col INTEGER NOT NULL PRIMARY KEY,
data_col INTEGER NOT NULL,

created_date DATETIME DEFAULT DATE() NOT NULL);

INSERT INTO Test1 (key_col, data_col, created_date)
VALUES (1, 1, #2020-12-31#);
-- created row with noncurrent date

UPDATE Test1
SET created_date = #1987-01-01#
WHERE key_col = 1;

-- changes also to noncurrent date

You say "That value will not change thereafter" but I just changed it!
Post back if you saw the current date, you may be running it
incorrectly.

The proper solution.

Logon to database as Admin, then

CREATE USER JohnV;

REVOKE ALL PRIVILEGES
ON TABLE Test1
FROM JohnV;

GRANT SELECT
ON TABLE Test1
TO JohnV;

CREATE PROCEDURE AddTest1
(:key_col INTEGER, :data_col INTEGER)
AS
INSERT INTO Test1 (key_col, data_col)
VALUES (:key_col, :data_col)
WITH OWNERACCESS OPTION;

GRANT UPDATE
ON OBJECT AddTest1
TO JohnV;

CREATE PROCEDURE UpdateTest1
(:key_col INTEGER, :data_col INTEGER)
AS
UPDATE Test1
SET data_col = :data_col
WHERE key_col = :key_col
WITH OWNERACCESS OPTION;

GRANT UPDATE
ON OBJECT UpdateTest1
TO JohnV;

Logon to database as JohnV, then

UPDATE Test1
SET created_date = #2002-02-02#
WHERE key_col = 1;
-- fails, cannot change created_date

INSERT INTO Test1 (key_col, data_col, created_date)
VALUES (2, 2, #2011-11-11#);
-- fails, cannot create row using created_date

EXECUTE AddTest1 2, 2;
-- success, created date is current

EXECUTE UpdateTest1 2, 999;
-- success, created date still is current

[tx sales]

ok now you have really lost me, I am new to the access stuff, and really have
no idea about anything you previously wrote. Thanks for trying to help, I am
looking to pay someone to fix the problem and the other 2 or 3 I have to be
fixed on the database

[pere...@jetemail.net]

tx sales wrote:
> ok now you have really lost me, I am new to the access stuff, and really have
> no idea about anything you previously wrote.

Bottom line: if you don't want anyone/anything (Form etc.) to change
you data, don't let them and that means using security.

[Duane Hookom]

In the other branch of this thread tx sales stated
================================

in the form section the requested info as follows:
Name: Invoice Date
Control Source:-Date()
Default Value:Date()

In the table section
Name: Invocie Date
Default Value: Date()

================================
Clearly the control source should be the name of the field whether it is
"Invocie Date" or "Invoice Date". The name of the control should be changed
to txtInvoiceDate. If this doesn't allow changing the value then the
recordset might not be updateable or a property value isn't allowing
updates.

--
Duane Hookom
MS Access MVP

<pere...@jetemail.net> wrote in message
news:1121766691.3...@z14g2000cwz.googlegroups.com...

[pere...@jetemail.net]

Duane Hookom wrote:
> In the other branch of this thread

yes, I'm just looking at the .tablesdbdesign

[John Vinson]

On 19 Jul 2005 02:19:02 -0700, pere...@jetemail.net wrote:

>An I think *you* are mistaken!
>
>The DEFAULT is only applied when the row is first created (INSERT INTO)
>and then only when no value was supplied. It don't stop users from
>changing it (UPDATE). Try this

Sorry... we were talking past one another.

The OP was indicating that *ACCESS ITSELF* was changing the date; the
reason is apparently that he had the Control Source of a textbox set
to Date(), and was not displaying the table field at all.

I misunderstood your issue; you're quite correct, of course, that the
stored date field can be manually edited. However, unless someone
takes deliberate action to change it, Access won't change it on its
own.

John W. Vinson[MVP]

[John Vinson]

On Tue, 19 Jul 2005 02:13:02 -0700, "tx sales"
<txs...@discussions.microsoft.com> wrote:

>Thanks John, I followed your instructions exactly and it keeps changing the
>date to the current date, it is not holding the date I created the record.
>All records have today's date.
>

You posted elsethread that the textbox has properties

in the form section the requested info as follows:
Name: Invoice Date
Control Source:-Date()
Default Value:Date()

The control source IS WRONG.

The Control Source property determines what will be displayed.

You have it set to Date(), so it will display today's date, regardless
of any field that is stored in your table.

Change the Control Source property to

[Invoice Date]

and you should be back in business.

John W. Vinson[MVP]

[pere...@jetemail.net]

John Vinson wrote:
> I misunderstood your issue; you're quite correct, of course, that the
> stored date field can be manually edited. However, unless someone
> takes deliberate action to change it, Access won't change it on its

Think about it the other way around: if there is no mechanism to change
the date (apart from the Admin table owner) then it cannot be changed
by Access, an Access user, an Excel user, an ADO user, etc. This is,
after all, the database/tables group and not the frontend/forms group :)