Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2003
    Posts
    107

    Exclamation Unanswered: arghh... newbie question

    I have a date field that I want to pre-populate with today's date when a new record is created.
    I've put the following in the control source property, I've put
    =Format(Now(),"mm/dd/yyyy")

    But maybe this is a bad thing? When i try to save the record, the system tells me that this field cannot be null.

    Also, I actually don't want "mm/dd/yyyy". Ideally, it should be "mm/dd/yy" but i noticed that whenever I do this, it's rejected by the table.
    Does the "date/time" data type only accept 4 digit dates? I'm going to be exporting this data to csv... so as long as I find a way to export it in the "yy" format, I don't care how it's saved in the database.
    any suggestions?

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    you are on the right path

    Store the date as a date and time format in the database you will thank me later also don't call the feild "date"

    when you go the export it you can format(datefeild,"mm/dd/yy")

    what i do is export as format(datefeild,"dd mmm yy") most importer will see this as a date

    I have had heaps of problem with 01/05 or 05/01
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Psst, dude...

    http://www.dbforums.com/showpost.php...51&postcount=5

    Default values rock
    George
    Home | Blog

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Sorry, but LOL!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by StarTrekker
    sorry but LOL!
    Care to explain?
    George
    Home | Blog

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Well... I just found your "Psssst... and subtle pointer to Default Value" funny!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Feb 2003
    Posts
    107

    default value

    sorry for being so thick but are you saying that I can set the default value of the field to "=Format(now(), "mm/dd/yyyy") ( or just to now(), i guess) instead of using the Control Source property?


    Also, I will take your suggestion and handle the formatting later on export.
    Thanks.

  8. #8
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    The problem you are/were having is the Control Source is resonsible for binding the form field to the data field in the table. In the Control Source you posted, you do not reference the table's field where the data will be stored. Therefore, the data is not being stored. When you try to save that data field has no value, so you get the error.

  9. #9
    Join Date
    Feb 2003
    Posts
    107
    ok. so i should change the control source to be the field in the table ... and should I use the "default value" property to have the "=Now()" value?

  10. #10
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Yes. That is what I would do. Now, you have a choice, set the default value at the form level or the table level. When you set it at the table level, no matter how you enter the data, using a form or directly in datasheet view, the default is added. If you have it at the form level, then different forms can have different default values. If that is not important then I would put it at the table level.

  11. #11
    Join Date
    Feb 2003
    Posts
    107

    using the default value...

    if i do this, (on the form or table level) what will happen if i'm navigating through previously created records? Will the system overwrite the old date and put today's date in it?
    I guess I could just test this on my own... sorry to bug you.

  12. #12
    Join Date
    Feb 2003
    Posts
    107
    it seems to be working just fine.
    Thanks for your help guys.

  13. #13
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You're not bugging me. The default value appears only when a new record is added. Existing data will not be changed.

Posting Permissions

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