Results 1 to 5 of 5
  1. #1
    Chris12 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    2

    Macro Code Help


    I am trying to make a macro that will display the total time that a machine has been down. It will increase the total days by one until the machine has been repaired. I have a weekly report that I need to send out once a week that will display all of our machine that are down.
    here are the fields that I have, Downed Machine Date, Date or Repair, and Total Days Down
    currently i am using DateDiff("d",[Downed Machine Date],[Date of Repair]) to show the down time but it wont show up until the machine has been repaired. So I need to alter the code so that when the repair date is blank then the field will show how long it has been down. and once it gets repaired then it will stop adding numbers as the days pass.

    Please let me know if i need to provide any more info.

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,902
    Try this:
    Code:
    DateDiff("d",[Downed Machine Date],IIF(IsNull([Date of Repair]),Date(),[Date of Repair]))
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,477
    Most of us here don't do macros, I think. Is that what you're really using or is it code?
    You refer to "fields" but don't say where they are? I presume a query. Possibly what you need is a calculated query field that uses IIF. The logic would be
    "If [repair date field] is null then subtract [date down] from Date, otherwise subtract [date down] from [date up]"
    If that makes sense, research IIF function. It's not possible to do much more than point you in that direction because of the lack of info in your post.

    If those are really your fields, advise to NOT have spaces in names. Also, don't use reserved words.
    One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
    What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  4. #4
    Chris12 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    2
    Thanks JoeM after altering my Code my Total days down field is displaying correctly. I was close to what you said in my tests but I just couldn't get the formatting down. Thanks again.
    Edit:
    Do you think there is a way to exclude the weekend days to the down time?

  5. #5
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,902
    You are welcome!

    Regarding weekend days, unlike Excel, Access does not have WORKDAYS and NETWORKDAYS function. However, some people have created their own Access User Defined Functions to do it.
    See: https://social.msdn.microsoft.com/Fo...orum=accessdev
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

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

Similar Threads

  1. Help with NPV Macro Code in VBA
    By BigNasty in forum Macros
    Replies: 6
    Last Post: 11-21-2016, 10:39 PM
  2. Macro Code Clean up
    By lonesoac0 in forum Macros
    Replies: 0
    Last Post: 08-01-2016, 07:37 AM
  3. How to run Data macro using VBA Code.
    By pritesharyan in forum Access
    Replies: 2
    Last Post: 06-18-2015, 09:17 AM
  4. VBA code/Macro help please.
    By Davidyam in forum Access
    Replies: 1
    Last Post: 02-26-2012, 09:59 PM
  5. Question about some code in a macro
    By AudiA4_20T in forum Programming
    Replies: 2
    Last Post: 07-11-2011, 08:16 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