1

I had a question but wasn't sure where I needed to post or view if someone has done this before. I have a dilemma on my hands.

Background Info: I have been trying to figure out what to do since I have a form that has a field where I use DateDiff to compare the current date to a field that has a measuring date. The difference between dates calculates the days open. I used DateDiff at first but then realize that I needed a way to stop the "Day's Open" when the status changed from a working (1) to a working (0).

Currently, I have a set of statuses (that have to be 0 or 1 for metric purposes and can only have a single 1 at any time) that are either working, dead or sold. Usually when the record is open, working is 1 and the open days field continues to increment. The other two fields are 0's if working is 1. If status changes, working turns to 0 and either dead or sold turn to 1.

My question: If that was to change (either working turns to 0 and sold or dead turns to 1), how could I get the days open to stop counting days?

I'm sort of new still to access and vb. I am the one with the most experience that was willing to take it on so that they wouldn't be using Excel anymore for the monthly metrics sheets.

Is there a way you can help? This is where I am stuck and its very specific problem that I have not been able to see in any of the forums. Your forums have been really helpful with the other parts of my access tasks! I do appreciate it being available.

Thanks, PPK

1

Counting days without explicitly storing the start date would require some kind of scheduled task to count them. This is not trivial to implement and not recommended.

I would suggest storing the date when opened and then doing a date diff between the start date and the current date to work out the number of days (including the appropriate rounding). I would suggest that it might be a good idea to also store the closing date.

Edit:

When the item is closed you have two options to work out/read the days opened in the future:

  1. You can store both the start date and the close date and do a datediff between them when you need to know the days opened.
  2. You can store the days open explicitly when you mark the item as closed.

I would recommend 1 as it is more concise.

Edit2:

To fully explain point 1. Basically you would use the datediff function and compare the start date to the current date, while specifying days (d) as the required unit.

In an SQL select query this would look something like:

select 
    datediff('d',table.StartDate,date()) as OpenDays,
    table.Status
from table

The date() function returns the current date.

Microsoft Access Help on DateDiff

  • Hmm. Well I am still trying to wrap my head about what your saying since I am still pretty new to access/vba. I have to use the date of measure against a current date in order to show "Days Open" but I can use the "Closed" date field instead of a status change to stop the "Days Open" from counting. How would I do this? That's why I am having a problem. Those "Day's Open" is what they track until the record has been closed. – PPK Feb 13 '14 at 21:21
  • See edited answer. – Zack Feb 13 '14 at 21:31
  • Thanks so much Zack for the information. I think I got it per your suggestion. I just have to do some testing on it to make sure that it accurately accounts for the stoppage when the closed date is entered. – PPK Feb 17 '14 at 19:18
  • I got what you said about doing the datediff between the open/closed dates to get the number of days open but my question I have is how would I get the days open to be counting when there is no closed date? That's what I am trying to figure out. – PPK Feb 18 '14 at 14:19
  • See edited answer. – Zack Feb 18 '14 at 21:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.