Page 2 of 2 FirstFirst 12
Results 16 to 27 of 27
  1. #16
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Unless I'm mistaken, this is all driven off the initial date? Is that appropriate? If a service is done late, should the next service of that type be done on the original schedule, or x weeks after it was last done? In my world, it would be the latter. If oil changes are scheduled to be done every 4k miles, and they're late doing the first one and do it at 6k, we don't want the next done at 8k. It will be done 4k after the last, at 10k (and somebody will get chewed on for doing the first late ).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  2. #17
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102
    Yes you are correct a report is produced based on the last inspection date and then in the case of HGV every 6 weeks, this is printed out every week for the workshop so they know what to pull in. The problem I am facing is we have other equipment i.e rail kit that has a strict inspection criteria, so needs weekly,monthly, 3monthly. 6monthly and yearly, so what I have if the code kindly given

    ServiceType: IIf(Len(Int(DateDiff("ww",[newjobdate],Date())/26))=Len(DateDiff("ww",[newjobdate],Date())/26),"6 Month Service",IIf(Len(Int(DateDiff("ww",[newjobdate],Date())/4))=Len(DateDiff("ww",[newjobdate],Date())/4),"1 Month Service",IIf(Len(Int(DateDiff("ww",[newjobdate],Date())/1))=Len(DateDiff("ww",[newjobdate],Date())/1),"1 Week Service","No Service")))

    This, when run, gives weekly and all the other inspection intervals, which looks great, but I need to be able to give a report to the workshop guys to tell them the inspection date and whether it is a weekly, monthly,3monthly etc, hope that makes sense.

  3. #18
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    When you run that in the report, it should give a ServiceType value for each car or equipment in the report on that specific day the report is run. Thought that is what you are giving the Workshop folks? Are you saying you want it to go into the future and give all the estimated dates for all the services a specific car will need?

  4. #19
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102
    Hi guys this is what I have and is working to a degree,

    ServiceType: IIf(Len(Int(DateDiff("ww",[delivery],Date())/24))=Len(DateDiff("ww",[delivery],Date())/24),"6 Month Service",IIf(Len(Int(DateDiff("ww",[delivery],Date())/4))=Len(DateDiff("ww",[delivery],Date())/4),"1 Month Service",IIf(Len(Int(DateDiff("ww",[delivery],Date())/1))=Len(DateDiff("ww",[delivery],Date())/1),"1 Week Service",IIf(Len(Int(DateDiff("ww",[delivery],Date())/48))=Len(DateDiff("ww",[delivery],Date())/48),"12 Month Service",IIf(Len(Int(DateDiff("ww",[delivery],Date())/12))=Len(DateDiff("ww",[delivery],Date())/12),"3 Month Service","No Service Needed")))))

    However it only shows the 1 week service, the 6 month service and the 1 month service, when the delivery date is 24 or 48 weeks it shows 1 month service or 6 month service instead of showing 3 month service or 12 month service, can anyone help?

  5. #20
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102
    This is what I have now

    ServiceType: IIf(Len(Int(DateDiff("ww",[delivery],Date())/24))=Len(DateDiff("ww",[delivery],Date())/24),"6 Month Service",IIf(Len(Int(DateDiff("ww",[delivery],Date())/4))=Len(DateDiff("ww",[delivery],Date())/4),"1 Month Service",IIf(Len(Int(DateDiff("ww",[delivery],Date())/1))=Len(DateDiff("ww",[delivery],Date())/1),"1 Week Service",IIf(Len(Int(DateDiff("ww",[delivery],Date())/48))=Len(DateDiff("ww",[delivery],Date())/48),"12 Month Service",IIf(Len(Int(DateDiff("ww",[delivery],Date())/12))=Len(DateDiff("ww",[delivery],Date())/12),"3 Month Service","No Service Needed")))))

    However neither the 3 month service nor the year service shows up, any ideas?

  6. #21
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    It is an If-Then-Else so you have to put them in order from longest interval to shortest as the first condition that matches will be used. So if you had 3 month before 6 month, in the actual 6th month the 3 month logic would be true again so would pick that and never go into the 6 month. So change it in order of 12 month, 6 month, 3 month, 1 month, 1 week order.

    So is [delivery] the date of the initial service as in your starting date or is that the date the last service was done?

  7. #22
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102
    Doh! thanks for that should have noticed that one sooner. the delivery date is the date at which it came on site, if I did have the last service date, which would better, does that change the dynamic of the statement or is it easiert o leave it be with the delivery date as the first initial date?

  8. #23
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    If you did not use the initial date and needed to key off the last date it was serviced, you would need to do some combination with the dates or some type of range maybe as you would never get to the higher service intervals if the target date kept moving. Another thing to note with this is that if a car say was due for weekly on a Monday but was unavailable and had to do service on Tuesday, the report for Tuesday would show that car as a "No Service". Not sure of your process and if that would happen that a car would not be available on the intended service date.

  9. #24
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102
    Bulzie, that works great now I get what I wanted, why I didn't see it, but hey this forum would be redundant haha

  10. #25
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Great, glad I could help!!

  11. #26
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102
    How do I close this with a resolved

  12. #27
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    In Thread Tools near the top of the screen.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Part Inspection Database Help
    By yoseph12 in forum Access
    Replies: 5
    Last Post: 12-20-2015, 02:21 PM
  2. Inspection report
    By FJM in forum Access
    Replies: 3
    Last Post: 09-24-2014, 05:50 AM
  3. Hydrant Inspection Database Help
    By Plan B in forum Database Design
    Replies: 12
    Last Post: 01-29-2013, 01:51 PM
  4. Time intervals
    By Dutch1956 in forum Queries
    Replies: 5
    Last Post: 07-12-2012, 11:21 PM
  5. Help with Time Intervals
    By ddog171 in forum Queries
    Replies: 3
    Last Post: 03-07-2006, 06:20 AM

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