Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 13 of 13

Thread: date

  1. #1
    Regular Coder
    Join Date
    Jul 2004
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts

    date

    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.

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Add "Triggers" (use the "help" to learn about them) that automatically set those fields when the table is changed.
    Check out the Forum Search. It's the short path to getting great results from this forum.

  • #3
    Regular Coder
    Join Date
    Jul 2004
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry for my ignorance. Is triggers available in Access. I did a search, and I couldn't find. Please explain. Thanks.

  • #4
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Check out the Forum Search. It's the short path to getting great results from this forum.

  • #5
    Regular Coder
    Join Date
    Aug 2002
    Location
    Texas
    Posts
    287
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

    fv

  • #6
    Regular Coder
    Join Date
    Jul 2004
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  • #7
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    A default value may work for "date added" but I don't think you'll find it working properly for a "date modified" field.
    Check out the Forum Search. It's the short path to getting great results from this forum.

  • #8
    Regular Coder
    Join Date
    Jul 2004
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #9
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Check out the Forum Search. It's the short path to getting great results from this forum.

  • #10
    Regular Coder
    Join Date
    Jul 2004
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That is to include the date modified field in the update statement? sorry I didn't understand that well.
    Last edited by needhelp26; 09-13-2004 at 11:33 PM.

  • #11
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by needhelp26
    That is to include the date modified field in the update statement?
    Exactly.

    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.
    Check out the Forum Search. It's the short path to getting great results from this forum.

  • #12
    Regular Coder
    Join Date
    Jul 2004
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #13
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You're trying to set the date function equal to a fieldname, it' supposed to go the other way round:

    Code:
    Update tablename set datemodified = date() where id = " & id
    Check out the Forum Search. It's the short path to getting great results from this forum.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •