Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    Dec 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    increment date automatically (Access 2002)

    Hi,

    I need some help here. Here's my scenario. I have 2 columns, which are PurchaseDate and ServiceDate. I want to be able to key in PurchaseDate and have ServiceDate set automatically to 6 months later, depending on PurchaseDate's date. I know the way to do it in CF, but not in Microsoft Access. Can anyone please help?

    Do appreciate any help offered. Thanks a lot.

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

    Re: increment date automatically (Access 2002)

    Why would you store the ServiceDate at all, since you can calculate it at any time by adding 6 months to the purchase date?
    Charlotte

  3. #3
    New Lounger
    Join Date
    Dec 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: increment date automatically (Access 2002)

    Charlotte, thanks for the reply. I just want to have ServiceDate to be 6 months later automatically after I entered PurchaseDate's date. Why? For convenience, I guess. Why would I calculate it manually when I can have it inserted automatically?

    Thanks.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: increment date automatically (Access 2002)

    Charlotte didn't mean that you should calculate the ServiceDate manually, but that you can have it calculated automatically.

    If the ServiceDate will *always* be 6 months after the PurchaseDate, you shouldn't have a ServiceDate field in the table, since it is derived information. Instead, create a query based on the table, and add a calculated column:

    ServiceDate: DateAdd("m", 6, [PurchaseDate])

    If you want the ServiceDate to be set to 6 months after the PurchaseDate, but give the user the possibility to change it, you should include a ServiceDate field in the table. You can set it when the PurchaseDate is filled in. The question is what should happen if the PurchaseDate is modified later on, for example because a mistake was made initially. Should the ServiceDate be modified automatically, or should it be left at the date set earlier?

  5. #5
    New Lounger
    Join Date
    Dec 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: increment date automatically (Access 2002)

    Hi Hans,

    Thanks a lot for the reply. You also replied to an identical post that I posted in the Excel forum. In your opinion, which one(Excel or Access) should I use to achieve this automatic date insertion?


    "If you want the ServiceDate to be set to 6 months after the PurchaseDate, but give the user the possibility to change it, you should include a ServiceDate field in the table. You can set it when the PurchaseDate is filled in. The question is what should happen if the PurchaseDate is modified later on, for example because a mistake was made initially. Should the ServiceDate be modified automatically, or should it be left at the date set earlier?"

    Yes, I would prefer some flexibility and changing of ServiceDate. If PurchaseDate is modified, ServiceDate should be also be updated to be 6 months later.

    I'm not very familiar with the syntax of Access. I do have experience with Access though, but it was merely storing data into it. All operations are controlled by other languages, such as ColdFusion and ASP.

    Do appreciate it if you can help. Thanks a lot.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: increment date automatically (Access 2002)

    Whether you want to do this in Access or Excel is up to you. Both can handle this kind of thing.

    Since you want the user to be able to modify the ServiceDate, you should include it as a field in your table. You must set it in a form based on this table, you can't do that in the table itself.
    <UL><LI>If you don't have a form based on the table yet, create one now.
    <LI>Open the form in design view.
    <LI>Select the text box bound to the PurchaseDate field.
    <LI>Activate the Properties window (View | Properties.)
    <LI>Activate the Event tab.
    <LI>Click in the After Update event.
    <LI>Select Event Procedure from the dropdown list.
    <LI>Click the builder button (the three dots ... to the right of the dropdown arrow.)
    <LI>You will be taken to the Visual Basic Editor, with the event procedure ready to be completed by you.
    <LI>Make it look like this:

    Private Sub PurchaseDate_AfterUpdate()
    Me.ServiceDate = DateAdd("m", 6, Me.PurchaseDate)
    End Sub

    If necessary, substitute the correct names.
    <LI>Switch back to Access (Alt+F11 or click in the appropriate icon in the Windows task bar.)
    <LI>Close and save the form.[/list]When the user enters or modifies the PurchaseDate, the ServiceDate will be set to 6 months later than the PurchaseDate automatically, but the user will still be able to modify the ServiceDate.

  7. #7
    New Lounger
    Join Date
    Dec 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: increment date automatically (Access 2002)

    Hi Hans,

    Your code worked great! Thanks a lot.

    Another thing, I would like to notify the user when ServiceDate is equal to the current date. Can this be achieved?

    Your code :

    Me.ServiceDate = DateAdd("m", 6, Me.PurchaseDate)

    What does the "Me" means? Is it a reserved word in VB?

    Thanks a lot. Really do appreciate it.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: increment date automatically (Access 2002)

    Me can be used in the code behind a form or report; it refers to the form or report that executes the code. The advantage of using it is that if you type Me followed by a period: <font face="Georgia">Me.</font face=georgia> Intellisense will kick in and display a list of properties and methods to choose from. Since controls are properties of a form/report, those are included in the list.

    When would you like to notify the user? When the user moves to a record for which ServiceDate = today? Or when the form is opened? Or when the database is opened?

  9. #9
    Star Lounger
    Join Date
    Apr 2003
    Location
    Morgantown, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: increment date automatically (Access 2002)

    Would it be equally effective to put the following in the properties for each field?

    PurchaseDate
    Format: Medium date (or whatever date format is preferred)
    Default Value: =Now()

    ServiceDate
    Format: Medium Date (or whatever date format is preferred)
    Default Value: =DateAdd("m",6,[PurchaseDate])

    I tried it on a form and it seemed to give me the desired result.

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

    Re: increment date automatically (Access 2002)

    You can reference another field in a form but you can't do it in a table.
    Charlotte

  11. #11
    New Lounger
    Join Date
    Dec 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: increment date automatically (Access 2002)

    Hans,

    Thanks a lot for the explanation. I would like to notify the user when the form is opened. I'm not too sure what it's like when you reffered to "when the database is opened". I'm thinking that upon opening the Access file itself?

    Bob, thanks for the suggestion. I do not wish to have the current date as a default value for PurchaseDate. I tried out your suggestion and it works, but when I remove the Now() as the default value for PurchaseDate, it no longer works.

    Charlotte, thanks. I get your point now.

    Thanks again.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: increment date automatically (Access 2002)

    I would create a query based on your table that returns all records for which the ServiceDate is today: set the criteria for ServiceDate to Date(). Save it as, say, qryServiceToday.
    Next, create a form based on this query. I would set its Popup and Modal properties to Yes, but that's up to you. Save it as frmServiceToday.
    In the On Load event of the form you created earlier, check if the new form should be displayed:

    Private Sub Form_Load()
    If DCount("*", "qryServiceToday") > 0 Then
    DoCmd.OpenForm "frmServiceToday"
    End If
    End Sub

  13. #13
    New Lounger
    Join Date
    Dec 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: increment date automatically (Access 2002)

    Thanks a lot Hans. One more thing, will all these compatible with earlier versions of Access?

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: increment date automatically (Access 2002)

    This should work without problems in Access 97 and up, and probably in Access 95 too. Earlier versions (Access 1 and 2) used Access Basic instead of VBA, so I doubt the code would work there without modification, but I have never used those versions, so I can't help you with that.

Posting Permissions

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