1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Default Value for table field in Access 2007

Discussion in 'Business Applications' started by slikbaz, Oct 4, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. slikbaz

    slikbaz Thread Starter

    Joined:
    Oct 4, 2010
    Messages:
    23
    I'm relatively new to access, only been working with it on and off for the past few months or so and have run into a little problem now.

    Basically, a date is entered in a field on a table called "Date of Entry", and another value is entered in a different field called "Serving Period". Now I have another field called "Expiry Date", and basically it's the value (date) of "Serving Period" added to the date in "Enter Date". I suppose that it would possibly have something to do with the Default Value of the "Expiry Date", but that's where I'm confused. Of course, I need not make it automatic and let the user do the math himself, but I'd rather it was done this way if possible.

    I'm using Access 2007 on Windows 7.

    Any help appreciated.
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    slikbaz, welcome to the Forum.
    Is the Serving Period in Days?
     
  3. slikbaz

    slikbaz Thread Starter

    Joined:
    Oct 4, 2010
    Messages:
    23
    Thanks, and no, the serving period is a numeric value in years.
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    That makes it a little more difficult as you have do extract the year from the date and add the serving period to it.
    But it can easily be doen using VBA.
     
  5. slikbaz

    slikbaz Thread Starter

    Joined:
    Oct 4, 2010
    Messages:
    23
    I haven't really explored VBA yet, so is it possible to write VBA code within access without the use of any external programs? And if so, can you point me towards the commands etc. that would be useful in doing what I need?
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I can write the code for you, but obviously it would be good to learn it for yourself. Access comes with it's own Version of VBA.
    If you can provide the exact Names of the Fields on the form I can create the code for you and you can paste it in where I instruct you to.

    You can download a Guide to using Access VBA from here
    http://support.microsoft.com/kb/222101
    Choose the Access version "ACMAIN11.chm,VBAAC10.chm".
     
  7. slikbaz

    slikbaz Thread Starter

    Joined:
    Oct 4, 2010
    Messages:
    23
    Yeah, I would definitely like to learn VBA. I'll give it a shot and be back here if I run into any problems. Thanks for the help :)
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    This is what you do in both Fields (as either could be changed) you enter the Code in the After Update Event Procedure.
    You can isolate the Date's Year by assigning it to a Variable using the
    currentyear = Year([Date of entry])
    and add on the Serving Period with
    newyear = currentyear + me.[Serving Period]

    Then you put the 2 parts back together using
    me.[Expiry Date] = left(me.[Date of entry], 6) & newyear

    The spelling and capitalisation of the Field names has to exact.

    Good Luck. (y)
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/954178

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice