Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    124

    Unanswered: date function problem

    I haven't used Access in awhile and I'm having a problem with a data field:

    Field name: dt
    DataType: Date/Time
    Format: Long Date
    Default Value: Now() -- I've also tried using Date()

    I just want to have the default date automatically entered when a record is inserted or updated.

    I keep getting the following error:
    'compile error. in table-level validation expression'

    Any help is appreciated.
    Thanks.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Where are you setting this default value, in the table or the form?
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Jan 2004
    Posts
    124
    Right now, I'm just creating the tables and relationships. So, I am placing the default value in the table.

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    When is the error being thrown?
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Jan 2004
    Posts
    124
    as soon as I try to save the table.

    I think the problem might be related to the reference library?

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If your data entry is always going to be via a form, the easiest way is to have the DateEntered field (dt) on the form and set the default value =date() or =now() if you want the time also stored (although you can also do this with the defaultvalue on the table itself with the right syntax - I think you just put =date() instead of just date()). Personally, I like doing it on the form - tells me if someone is bypassing the form to enter a record. This will handle the new record situation. In regards to updating, have another field in your table called: DateModified (or something like that) and then on the form, in the AfterUpdate event of the form, do some vba code such as me!DateModified = date(). It won't tell you what was updated but it will tell you that somebody made some change to something on that record on that date (there's a little bit more involved in seeing what field was modified and oldvalue verses newvalue - I have a kind of nice sample of a somewhat more in-depth mdb someone distributed which logs this into a "changelog" type of table - there's different ways you can do this if you want to get to that level of detail). You may also want to consider having an EnteredBy and ModifiedBy field which populates the user who added/modified the record (See the posts on getting the user LoginID/name where you would just set the defaultvalue =getuser() on a post I have on this). I would have these fields locked on the form. I consider the standard fields on any important data tables to be: DateEntered, EnteredBy, DateModified, ModifiedBy.
    Last edited by pkstormy; 09-13-06 at 16:27.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Now() should work fine as a table-level default value.

    very occasionally, Access error messages are correct! are you sure you don't have something strange in Validation Rule/Validation Text ??

    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
  •