Results 1 to 14 of 14
  1. #1
    jdanieluk is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    22

    Update date field certain number of months based on another fields value

    Hello everyone, I am new to this site and very much a novice with Access. I appreciate any help that is given.

    My problem is that is that I have a date field I am trying to update a certain amount of months at a time. The amount of months is stored in another field. I need the update to loop some how to make my date field current.

    This is ran from a form button event in VBA.
    Using Access 2013
    ``
    Here is a little background on the fields pertaining to the update.
    [Installed] = Date of installation
    [Chosen Exchange] = Number of months until next exchange.
    [Exchange] = Date Exchange is due
    [Exchange_MOS] = Number of months between [Installed] and [Exchange]
    ``
    I can use this;


    Me.[Exchange] = DateAdd("m", Me.[Chosen Exchange], Me.[Exchange])

    This works, but if the item is past the exchange date considerably, it will only add the set amount of months. (i.e. If the [Exchange] date was 01-01-16 and the [Chosen Exchange] was 6 months, this will only add 6 months. The new [Exchange] date then becomes 07-01-16 which is still past due. I need it to loop another cycle if it is less than 6 months, or whatever the [Chosen Exchange] is, and make it current.) I hope that makes sense.

    What I need to do;
    [Exchange] date is 01-01-16
    [Chosen Exchange] 6 months
    [Installed] date is 03-08-17
    Loop in [Chosen Exchange] month increments?
    [Exchange] date becomes 07-01-17
    ideally if the [Exchange] date is greater than a certain percentage of the [Chosen Exchange]. This way if the [Chosen Exchange] is 6 months but the next [Exchange] date is only 1 or 2 months out, I would want it to add another cycle. This would make the [Exchange] 7 or 8 months.

    I tried to accomplish something this way but the loop hangs up the program. I think the logic is wrong. LOL
    Do While Me.[Exchange_MOS] <= Me.[Chosen Exchange]
    Me.[Exchange] = DateAdd("m", Me.[Chosen Exchange], Me.[Exchange])
    Loop

    Thanks in advance for any help.

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Can you restate again.

    What I need to do;
    [Exchange] date is 01-01-16
    [Chosen Exchange] 6 months
    [Installed] date is 03-08-17
    Loop in [Chosen Exchange] month increments? - What does this mean?
    [Exchange] date becomes 07-01-17 - Why does Exchange date become 7/1/17. Does it have to be greater then current date but you have to still use the 6 month increments from Chosen Exchange? So bacially it added 18 months(3xChosen Exchange?)

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    jDanielUK,

    Welcome to the forum.
    In order for readers to offer focused responses, it always helps to give some business context for your issue.
    My problem is that is that I have a date field I am trying to update a certain amount of months at a time.
    That may be the detailed issue from your perspective, but readers know nothing about the proposed database, or the business/process that you are trying to solve with this approach.
    If you tell us in plain, simple English about your business and WHAT you are trying to accomplish, there may be options.

    Good luck with your project.

  4. #4
    jdanieluk is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    22
    Sorry about that.

    The Database is used for parts tracking and swapping out installed parts on machines that need to be exchanged out on set monthly intervals.

    I have a date field that is used for the exchange date for the part. I have another field that holds a numeric value for the monthly exchange interval. That value can range from 0 months to 48 months, usually in 6 month intervals. I need the exchange date for the part to be advanced to the next current exchange interval when the part is changed out.

    As an example; If Part A is set for an exchange date of 1-1-16 and the exchange interval is every 6 months. If i changed the part out today, I can advance the new exchange date to 7-1-16 but i need it to be current and not still past due. I want it to continue to advance the new exchange date until it is current. 1-1-16 becomes 7-1-16 then becomes 1-1-17 and the new current date should be 7-1-17.

    This statement will work for me, but then the new exchange date for the part is not current.
    Me.[Exchange] = DateAdd("m", Me.[Chosen Exchange], Me.[Exchange])


  5. #5
    jdanieluk is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    22
    Quote Originally Posted by Bulzie View Post
    Can you restate again.

    What I need to do;
    [Exchange] date is 01-01-16
    [Chosen Exchange] 6 months
    [Installed] date is 03-08-17
    Loop in [Chosen Exchange] month increments? - What does this mean?
    [Exchange] date becomes 07-01-17 - Why does Exchange date become 7/1/17. Does it have to be greater then current date but you have to still use the 6 month increments from Chosen Exchange? So bacially it added 18 months(3xChosen Exchange?)
    [Exchange] is a date field that is used for the exchange date of a part on the machine
    [Chosen Exchange] is a numeric field used to hold a monthly interval to exchange this part out
    [Installed] is a date field that shows the date the part was installed on the tool
    [Exchange_MOS] I also have this numeric field that stores the difference in months between the Installation date and the exchange date

    Loop in [Chosen Exchange] month increments? - What does this mean?
    I was trying to show that I need the new Exchange date for the part to somehow loop through the exchange cycle to become current.

    [Exchange] date becomes 07-01-17 - Why does Exchange date become 7/1/17. Does it have to be greater then current date but you have to still use the 6 month increments from Chosen Exchange? So bacially it added 18 months(3xChosen Exchange?)
    Yes, that is correct. I need the new exchange date to keep advancing so it is past the current installed date but still close to the monthly interval set in the [Chosen Exchange] field. We have preventive maintenance on the tools that we try to swap out the parts needing to be exchanged. So we try to keep all of the maintenance on the same monthly schedules.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Maybe it's just me, but this is not clear.

    As an example; If Part A is set for an exchange date of 1-1-16 and the exchange interval is every 6 months. If i changed the part out today, I can advance the new exchange date to 7-1-16 but i need it to be current and not still past due. I want it to continue to advance the new exchange date until it is current. 1-1-16 becomes 7-1-16 then becomes 1-1-17 and the new current date should be 7-1-17.
    If Part A is set for an exchange date of 1-1-16 and the exchange interval is every 6 months. If i changed the part out today, I can advance the new exchange date to 7-1-16 but i need it to be current and not still past due.
    Today being 8 Mar 2017, so the exchange process has not been executed for over a year--is that what this represents?
    If so, then you want to go into to the records and bring all existing exchange dates up to current date while respecting the Exchange Period/Frequency (6 months). This seems that you're trying to correct records that may or may not have been exchanged according to your "rules".

    Is that a proper understanding of the issue?

    If so, then I think you need some Loop construct as you mentioned in post 1.

    General logic as I understand it (untested)
    Code:
    For a given machine
       For each Part to be swapped
           If exchangeDate < Date then
               do while exchangeDate < Date
                     PartExchangeDate = DateAdd("m", ExchangeFrequency_M,PartExchangeDate)
               Loop
           End if
         Next Part
     Next Machine

  7. #7
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    I think I see where you are going. But if you change it today 3/8/2017 and it has a 6 month interval, wouldn't you want the new exchange date to be 6 months from now or 9/8/2017? If not couldn't you have a situation where you changed it today, but adding 6 months from the last exchange date could say put a date of 4/1/2017 if it worked out that way meaning it would want you to change it out again only in 1 month?

  8. #8
    jdanieluk is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    22
    Quote Originally Posted by orange View Post
    Maybe it's just me, but this is not clear.



    If Part A is set for an exchange date of 1-1-16 and the exchange interval is every 6 months. If i changed the part out today, I can advance the new exchange date to 7-1-16 but i need it to be current and not still past due.
    Today being 8 Mar 2017, so the exchange process has not been executed for over a year--is that what this represents?
    If so, then you want to go into to the records and bring all existing exchange dates up to current date while respecting the Exchange Period/Frequency (6 months). This seems that you're trying to correct records that may or may not have been exchanged according to your "rules".

    Is that a proper understanding of the issue?

    If so, then I think you need some Loop construct as you mentioned in post 1.

    General logic as I understand it (untested)
    Code:
    For a given machine
       For each Part to be swapped
           If exchangeDate < Date then
               do while exchangeDate < Date
                     PartExchangeDate = DateAdd("m", ExchangeFrequency_M,PartExchangeDate)
               Loop
           End if
         Next Part
     Next Machine
    "Today being 8 Mar 2017, so the exchange process has not been executed for over a year--is that what this represents? Yes, the part can be way past the exchange date and more than the exchange interval in certain cases where the part has not been changed out for a certain reason.

    If so, then you want to go into to the records and bring all existing exchange dates up to current date while respecting the Exchange Period/Frequency (6 months). This seems that you're trying to correct records that may or may not have been exchanged according to your "rules".

    "Is that a proper understanding of the issue?" Yes. The only difference would be this is one record at a time from a form using a query. But definitely what I am trying to do.

    I think this is what I was trying to work through but i got caught up on comparing the dates in the beginning of the loop.
    "
    If exchangeDate < Date then
    do while exchangeDate < Date
    PartExchangeDate = DateAdd("m", ExchangeFrequency_M,PartExchangeDate)
    Loop
    End if
    "
    Let me try to work with what you supplied.

  9. #9
    jdanieluk is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    22
    Quote Originally Posted by Bulzie View Post
    I think I see where you are going. But if you change it today 3/8/2017 and it has a 6 month interval, wouldn't you want the new exchange date to be 6 months from now or 9/8/2017? If not couldn't you have a situation where you changed it today, but adding 6 months from the last exchange date could say put a date of 4/1/2017 if it worked out that way meaning it would want you to change it out again only in 1 month?
    Yes, I could run in to that problem, and that will be my next problem to figure out. But I need the exchange date to stay within the current monthly interval so it will match up with the other work on the tool and everything can be done at the scheduled down time for the tool. If i just add 6 months to the current date then the scheduled down time for the tool to do other maintenance will not match up.

    As you mentioned, if the next generated Exchange date is only 1 or 2 months away, I would prefer it to loop to the next exchange date. The new exchange date for the part would then become 7 or 8 months out instead of 1 or 2.

  10. #10
    jdanieluk is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    22

    Thumbs up

    This is what I came up with and it somewhat works. The problem I see now would be if my exchange cycle was 12 months and the next exchange date happened to be 10 months away, I don't want it to run the update again and become 22 months.

    Code:
    Do While Me.[Exchange] < Me.[Installed] 
        Me.[Exchange] = DateAdd("m", Me.[Chosen Exchange], Me.[Exchange])
    Loop
    
    If Me.[Exchange_MOS] <= [Chosen Exchange] Then
        Me.[Exchange] = DateAdd("m", Me.[Chosen Exchange], Me.[Exchange])
    End If
    Is there any way to do a mathematical comparison on the If statement? Say if [Exchange_MOS] was 9 and the [Chosen Exchange] was 12 it wouldn't run again, but if the [Exchange_MOS] was less than 30% of the [Chosen Exchange] number it would run again?

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I think you have to review and test the facts involved. We're sort of guessing at the "facts".

    If your ExchangeDate is > than Today, the the loop shouldn't add anything.
    However, if there are other conditions --we had to replace Part X 3 weeks before the scheduled exchangedate, then we're talking about additional conditions.

    You may have proposedExchangeDate that is based on the last ExchangeDate and the Frequency, and an ActualChangeDate where ActualChangeDate is the Date on which a broken Part was exchanged, thenyour next exchangeDate would be that date + the frequency value.

    Any arithmetical/mathematical expression will be based on the "facts of your exchange policy".

  12. #12
    jdanieluk is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    22
    This seems to be working although it is not pretty and I am sure there is an easier way. I just need to fine tune a few things. I think my inexperience with writing code will show.

    Code:
    Private Sub Trap_Exchange_Click()
    Dim TrapDate, Message1, Title1, TrapSN, Message2, Title2, MOSMult
    Message1 = "Please enter the date of the Trap Exchange.(MM/DD/YYYY)"
    Title1 = "Work Completed Date"
    TrapDate = InputBox(Message1, Title1)
    Me.[Pump Exchange Date] = TrapDate
    
    
    If Me.[Chosen Exchange] <= 12 And Me.[Chosen Exchange] > 6 Then MOSMult = 0.17
    If Me.[Chosen Exchange] <= 6 And Me.[Chosen Exchange] > 3.1 Then MOSMult = 0.25
    If Me.[Chosen Exchange] <= 3 Then MOSMult = 0.35
    
    '
    '
    '
    '
    Me.[Installed] = [Pump Exchange Date]
    
    '
    '
    '
    Do While Me.[Exchange] < Me.[Installed]
      Me.[Exchange] = DateAdd("m", Me.[Chosen Exchange], Me.[Exchange])
    Loop
    
    
    If Me.[Exchange_MOS] <= [Chosen Exchange] * MOSMult Then
        Me.[Exchange] = DateAdd("m", Me.[Chosen Exchange], Me.[Exchange])
    End If
    
    
    If Me.[Chosen Exchange] <= 12 And Me.[Chosen Exchange] >= 6 Then
        Do While Me.[Exchange_MOS] <= Me.[Chosen Exchange] * 0.74
            Me.[Exchange] = DateAdd("m", 3, Me.[Exchange])
        Loop
    End If
    
    '
    '
    '
    
    
    End Sub

  13. #13
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    I think my inexperience with writing code will show.
    We have all been there but the only real way you learn is by trying different things and seeing what works and what does not. Access has good tools to let you debug code and such.

    One thing on this code:
    If Me.[Chosen Exchange] <= 12 And Me.[Chosen Exchange] > 6 Then MOSMult = 0.17
    If Me.[Chosen Exchange] <= 6 And Me.[Chosen Exchange] > 3.1 Then MOSMult = 0.25
    If Me.[Chosen Exchange] <= 3 Then MOSMult = 0.35

    You should probably do it in Else statements so it does not run thru code it does not have to. Also reversed it to hit the smaller conditions first:

    If Me.[Chosen Exchange] <= 3 Then
    MOSMult = 0.35
    Else
    If Me.[Chosen Exchange] <= 6 And Me.[Chosen Exchange] > 3.1 Then
    MOSMult = 0.25
    Else
    If Me.[Chosen Exchange] <= 12 And Me.[Chosen Exchange] > 6 Then
    MOSMult = 0.17
    End If
    End If
    End If

  14. #14
    jdanieluk is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    22
    That makes a lot of sense. The way you showed seems a lot cleaner. I never thought to do it that way. Thanks for all of the great ideas.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Update Field based on Max Date
    By janmack79 in forum Queries
    Replies: 27
    Last Post: 01-18-2017, 01:07 PM
  2. Replies: 5
    Last Post: 01-18-2016, 07:56 AM
  3. Replies: 9
    Last Post: 04-13-2012, 10:10 AM
  4. Replies: 4
    Last Post: 04-18-2011, 07:18 AM
  5. Replies: 11
    Last Post: 08-04-2010, 04:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums