Calculating the total amount of working days between two dates

[BSC5905]

I have created a table that consists of 5 fields. Three of the fields are text boxes and the other two are combo boxes. The two text boxes, “txtCR_SubmittedDate” and “txtCR_ClosureDate” are date fields. Listed below are the 5 fields that I mentioned above.

txtCR_Number
txtCR_SubmittedDate
txtCR_ClosureDate
cboCR_Status
cboCR_Creater

The combo box, “cboCR_Status” consist of two choices. They are Inprocess and Completed. When “Inprocess” has been selected from the combo box, “cboCR_Status” and text box “txtCR_ClosureDate” is empty, I would like to calculate the total amount of working days between the “txtCR_SubmittedDate” and the current date.

When “Completed” has been selected from the combo box, “cboCR_Status” and text box “txtCR_ClosureDate” has a date in it, I would like to calculate the total amount of working days between the “txtCR_SubmittedDate” and “txtCR_ClosureDate”.

I would like for the calculation to be shown in a report. Can this be done and how? Also would this have to be done in a query?


Thank you for any assistance.

 

[lespaul]

first, you should read The Evils of Look Fields in Tables.

secondly, it is much easier to calculate the differences in dates if the fields are defined as date and not text.

You indicate that you want to calculate the number of working days - this is not something you can do at the table level - in fact, you should not allow users access to the tables, you should develop forms that allow them to interact with the tables. At the form level you can calculate the difference and update the table with that information, but you can't perform the calculation at the table level.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[MichaelRed]

mixed metaphors are confusing. tables have fields. forms (and reports) have controls (e.g. text boxes and combo boxes). although it is POSSIBLE to use controls in tables, the practice is somewhat discouraged - particularly for text boxes (no 'advantage' there at all).

you could actuall do a small bit of (re)search right here (in Tek-Tips) where there is (for sure) a FAQ re working days. and, it has been referenced frequently enough in the threads to make it easy to find.

on the other hand, the questrion statement leads me to suspect that implementation of the code in the FAQ would be a challenge for you.





MichaelRed

 

[BSC5905]

Sorry for the wrong terminolgy but the 5 features that I listed above are controls on a form. As for the two date controls on the form, “txtCR_SubmittedDate", and "txtCR_ClosureDate" there data types in the uderlying table are set to date/time. So is it posible to calculate the amount of working days between theses two controls on a form?

 

[lespaul]

Yes it's possible, see FAQ181-261 on how to do it.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi