View Full Version : date

09-09-2004, 04:01 AM
I want to add a data field to my Access database and capture date entered and date modified using vbscript. how do I do it? thanks.

Roy Sinclair
09-09-2004, 05:27 PM
Add "Triggers" (use the "help" to learn about them) that automatically set those fields when the table is changed.

09-09-2004, 11:57 PM
Sorry for my ignorance. Is triggers available in Access. I did a search, and I couldn't find. Please explain. Thanks.

Roy Sinclair
09-10-2004, 05:06 PM
Which version of Access? I opened Access on my machine and searched for Triggers before I wrote my previous post because I found triggers in the documentation (Access 2003).

As a FYI, triggers is the mechanism used to properly support this functionality in all the large database products. If the database product you're using doesn't support triggers then perhaps you've outgrown that product and you should start planning how you'll move past it before more of it's shortcomings start having larger impacts on your development.

The idea behind triggers is that you can add a bit of code that'll automatically insert the current date into the date added field of a record being inserted or into the date updated field of a record being changed. Without triggers you'll be forced to ensure that every program or function that adds or updates the database also includes the code to set the dates. Usually that means using the SQL date function as the source of the value to be inserted into the date field.

09-10-2004, 05:55 PM
Could you not also define a default value for that/those date fields
as Current Date if no date is entered? Most RDBMS allow for that.
I believe Access allows this.


09-11-2004, 11:41 PM
Thankyou for both your feedbacks. It is quite interesting to learn a lot of knowledgeable information. I have to check again for triggers in my Access database. If I have to add default date value(date()) to their appropriate fields i.e date entered and date modified fields, will it work?

Roy Sinclair
09-13-2004, 06:19 PM
A default value may work for "date added" but I don't think you'll find it working properly for a "date modified" field.

09-13-2004, 10:59 PM
what could be the best way to date modified field except triggers? Triggers comes along with Access 2003 and it is not available in 2000 version.

Roy Sinclair
09-13-2004, 11:20 PM
When you start running into the limits of your database engine you really should look at upgrading to a more capable engine (MS Access 2003 may be improved but it's still not a production class database engine). Unfortunately that may not be easy to accomplish in the middle of another project.

The only way to get modified date without triggers is to find all the places in your code where you modify a field and ensure that you also use the date function in SQL to update the modified date field in the same SQL statement you use to modify the other field(s) in the database record.

09-13-2004, 11:30 PM
That is to include the date modified field in the update statement? sorry I didn't understand that well.

Roy Sinclair
09-14-2004, 03:57 PM
That is to include the date modified field in the update statement?


The advantage offered by using triggers in databses that support them is that you don't have to add anything to the INSERT and UPDATE statements. The disadvantage you face in not having triggers is that any UPDATE statement which you don't find and add the date modified field to can update a record without updating the date.

09-15-2004, 04:02 AM
I tried and it didn't work properly. I am getting data mistype error or something like that. What I did:

Update tablename set date = '"& datemodified & "' where id = " & id 'datemodified is the field name.

I also tried date = #datemodified# but no luck. What is the correct syntax for the date? Thanks.

Roy Sinclair
09-15-2004, 05:44 PM
You're trying to set the date function equal to a fieldname, it' supposed to go the other way round:

Update tablename set datemodified = date() where id = " & id