Macro Code Help

[Chris12]

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.

[JoeM]

Try this:

DateDiff("d",[Downed Machine Date],IIF(IsNull([Date of Repair]),Date(),[Date of Repair]))

[Micron]

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

[Chris12]

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?

[JoeM]

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