Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2005
    Posts
    1

    Question Unanswered: problem with date expression

    I was hoping someone could help me on this one. I am fairly new to Access. I have an Access db set up. It is being used to track project tasks. There is a text field for Notes (called Notes) where we can provide status updates on the project. There is also a Date Updated field. This is used to identify when this record was last updated. Well, unfortunately the Date Updated field (named simply Date) rarely gets filled in. So I'm trying to make it automatically update to the current date when changes are made in the Notes field. So I have set an "on change" action on the Note field. The expression says
    Code:
    me.Date=Now()
    Now, this works perfectly fine. But here's the problem. I do not want the time displayed in this field. I have limited both the form and table to use only the Short Date format. Any they do. But the time data is still there and when we view this database with the web front-end it displays the time. So I have tried to make the expression be
    Code:
    me.Date=Date()
    but when I do this it removes the () and the field does not update. If I make the expression say
    Code:
    me.Date=Today()
    I get an invalid sub error. So, to make a long story short, what can I use in place of Now() to get just the date?

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Select the date format you want within the Format property of your field or you can.....

    Me.Date = Format(Now(), "Short Date")

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Nov 2005
    Posts
    4
    This would work good in my DB. Where and how do I do this? Detailed instructions please... Thanks

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Like many database engines the default data storage method in Access has no concept of date, or time, what it does have is a date/time datatype which stores the number of seconds since a sepcific date. So you implicitly store a time and date in a date / time column. As Cuyberlynx suggets you control what is represented by a date or time format eg mydatevalue=format(mydatecolumn,"DD-MMM-YYYY")

    have a look in the help system for date and time formats.

    Zane_H, what you could do is place some code in the forms before (or after) update event, rather than on change events. The reason - its possible to cancel changes by pressing the escape key.

    You might want to consider a differnet methodology, perhaps consiidering having your notes column as a sub field, with multiple notes per parnet item. The reason - you then timkesamp the note at the time the note was created,

  5. #5
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    The reason the Date function does not work, is probably because there is alredy an object on the form called Date ...

    Most naming conventions contain at least the following
    * do not use reserved words as names of objects
    * every form control you're going to manipulate or reference (through expressions, queries, code ...) should be named something different than the field they are bound to

    Also - be careful with implicit cast

    If you have a control bound to a date field, and you do the following

    Me!txtDate.value = format(date, "Short Date")

    this is what you do
    1 - format a perfectly valid date to a string representation of the date, according to the regional settings of the system
    2 - perform an implicit cast of that string to a date when assigning to the control

    This is not necessary, a valid date can be assigned to a date field, variable or control anytime (note what healdem says about how dates are stored internally), and I wouldn't be surprised if it would cause grief later on ...

    Some workarounds for those still wanting to use "illegal" names

    Me!Date = VBA.Date
    Me!Date = Int(VBA.Now)

    Agree with healdem, the before update event of the form is probably the best place for it.
    Roy-Vidar

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    just being pedantic...
    A's datetime does not store seconds since anything at all.
    it stores days-decimal-fractional days

    i.e.a datetime of X.25 is 6AM on the Xth day.

    this should not be a concern unless you plan to cheat and manipulate datetimes directly -- adding 60 to an access datetime adds 60 days, not 60 seconds -- but you shouldn't be directly manipulating datetimes anyway, should you

    izy
    currently using SS 2008R2

Posting Permissions

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