Date Stamp

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
For verification that my clerical staff are inputting our client data on the
date the client first came, I need an automated date stamp, that will return
the date the data was input.
 
If you are saving this in a table field:
Go into table design and select the field. (It must be set to type:=
Date/Time)
Enter Now() or Date() in the Default Value.

If you are using a data entry form:
Open it in design mode. Right click on the field and select properties.
On the Data tab..
Enter Now() or Date() in the Default Value.

Now() will give you the full date (12/25/2006 12:00:01 AM)
Date() will give you just the date (12/25/2006)
Time() will give you just the time (12:00:01 AM)

If your table field name is "Date" or "Time"
Be sure to use [ ] brackets around the field names in queries and vba code
when referring to fields that are using these reserved words as the column
header so that Access can distinguish between your table field name and the
built-in function.

jmonty
 
If your table field name is "Date" or "Time"
Be sure to use [ ] brackets around the field names in queries and vba code
when referring to fields that are using these reserved words as the column
header so that Access can distinguish between your table field name and
the
built-in function.
Click to expand...

Better yet - Change it to DateStamp or TimeStamp - for clarity (BirthDate,
HireDate etc.) and the issue about reserved words.

--
Craig Hornish

Visit weekly Access conferences
Tuesday, 11:00am-12:30pm (Eastern US)
Thursday, 3:30pm- 5:00pm (Eastern US)

http://community.compuserve.com/n/pfx/forum.aspx?webtag=ws-msdevapps

"Think outside the box, because anything is possible."
"How long it will take or whether it requires divine intervention is another
issue"

jmonty said:
If you are saving this in a table field:
Go into table design and select the field. (It must be set to type:=
Date/Time)
Enter Now() or Date() in the Default Value.

If you are using a data entry form:
Open it in design mode. Right click on the field and select properties.
On the Data tab..
Enter Now() or Date() in the Default Value.

Now() will give you the full date (12/25/2006 12:00:01 AM)
Date() will give you just the date (12/25/2006)
Time() will give you just the time (12:00:01 AM)

If your table field name is "Date" or "Time"
Be sure to use [ ] brackets around the field names in queries and vba code
when referring to fields that are using these reserved words as the column
header so that Access can distinguish between your table field name and
the
built-in function.

jmonty


Mark Napier said:
Hi,
For verification that my clerical staff are inputting our client data on
the
date the client first came, I need an automated date stamp, that will
return
the date the data was input.
Click to expand...
Click to expand...
 
Craig said:
If your table field name is "Date" or "Time"
Be sure to use [ ] brackets around the field names in queries and vba code
when referring to fields that are using these reserved words as the column
header so that Access can distinguish between your table field name and
the
built-in function.
Click to expand...

Better yet - Change it to DateStamp or TimeStamp - for clarity (BirthDate,
HireDate etc.) and the issue about reserved words.
Click to expand...

....and the issue about reserved words remains.

TIMESTAMP is a standard SQL data type, synonymous with Access/Jet's
DATETIME type. Acess/Jet's big sister, SQL Server, has a TIMESTAMP
9so-called) data type. Hence, TIMESTAMP is a reserved word in Jet 4.0,
SQL Server, SQL-92, SQL-99, SQL-2003 and ODBC.

Jamie.

--
 
jmonty said:
If you are saving this in a table field:
Go into table design and select the field. (It must be set to type:=
Date/Time)
Enter Now() or Date() in the Default Value.

If you are using a data entry form:
Open it in design mode. Right click on the field and select properties.
On the Data tab..
Enter Now() or Date() in the Default Value.
Click to expand...

And what if they are not e.g. the users has permissions to connect via
Excel, VBA, etc?

Say you wanted to maintain a 'date inserted' column
for a table:

CREATE TABLE MyTable (
key_col INTEGER NOT NULL UNIQUE,
data_col INTEGER NOT NULL,
effective_date DATETIME DEFAULT NOW() NOT NULL
);

Obviously you don't want the effective_date column to be written to
directly e.g. a user could accidentally (or otherwise) enter a far
future date; let's also say this metadata need not be exposed either,
so you wrap it in a view/query:

CREATE VIEW MyFakedTable
AS
SELECT key_col, data_col
FROM MyTable;

Revoke all permissions from the table and grant them to the view/query.

Users can now insert into the view/query and the default 'timestamp'
gets automatically applied:

INSERT INTO MyFakedTable (key_col, data_col) VALUES (1, 1);

Of course, you are more likely to want a 'last modified' date i.e. the
effective_date column is additionally maintained for updates. For
Access, a stored proc/parameterized query is required e.g.

CREATE PROCEDURE SetMyTableData (
arg_key_col INTEGER,
arg_data_col INTEGER
) AS
UPDATE MyTable
SET data_col = arg_data_col,
effective_date = NOW()
WHERE key_col = arg_key_col;

Obviously you would now revoke update permissions from the view/query.

Jamie.

--
 
Back
Top