Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Ontario, Canada
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Computing the default value for a field (Access 97)

    <img src=/S/frown.gif border=0 alt=frown width=15 height=15>
    I would like to have a field value in my Access 97 database automatically calculated based on the value in two other fields. The first field would be a date and the second field would be a period of days. For example, let's say we have field one as "Date Entered", field two as "Days to Complete" and then the last field, the one that needs to be calculated by adding the other two would be called "Date Due".

    How do I write the formula for field "Date Due" to add the other two field values and present a date?

    THANKS!!

    Drew

  2. #2
    Star Lounger
    Join Date
    Sep 2001
    Location
    SC, USA
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Computing the default value for a field (Access 97)

    Just add the two fields:
    Expr1: [Date Entered]+[Days to Complete]

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Computing the default value for a field (Access 97)

    Another approach is to use the DateAdd function. It is more useful if you want to add weeks or months (or years) as it lets you specify the interval you want to add. Check the help file for the syntax.

    Another trick that doesn't seem to be documented very often is that you can set a date equal to the last day of the month by specifying zero (0) for the day parameter in the DateSerial function. Very useful for dealing with leap years and Feb, April, Sept and Nov.

    Lastly, as long as you want to do dynamic calculations, you don't need to store the results in a field in a table - just put the expression in a query.
    Wendell

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Location
    Ontario, Canada
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Computing the default value for a field (Access 97)

    Well, that's precisely what I tried and it gave me an error message. If I am in Table Design mode, where am I to put that expression? Perhaps that's where I erred.

    Drew

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Computing the default value for a field (Access 97)

    Right. I don't think table definitions allow calculated fields. As suggested above, do the calculation in a query based on the table or as the control source for a form or report field.

    Besides, in general, it's good practice not to bloat your tables too much with data that's directly calculatable from other fields in the record.

    Tom

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Computing the default value for a field (Access 97)

    You cannot create default values in a table that are dependent on another field in the table. The logical place to do this is in a form, which is where you should be entering data anyhow. However, as has been pointed out, it is bad design to capture data that can be calculated on the fly. If you did capture the calculated value, then it would need to be updated whenever anyone changed the field it was dependent on. And there is *no* way to do that in a table, only with a query or using a form.
    Charlotte

  7. #7
    Star Lounger
    Join Date
    Feb 2001
    Location
    Ontario, Canada
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Computing the default value for a field (Access 97)

    Thanks everyone for your help! I see now where I was in error. It makes sense not to have a permanent calculated field in the data tables. I tried adding a text box to a form and set the control to the formula and bingo! it worked fine.

    THANKS AGAIN!

Posting Permissions

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