Ms-access – Calculating Open Days field and stopping with status change

fieldsms accessvba

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

Best Answer

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

Related Question