Tech Support Guy banner
Status
Not open for further replies.

MS Access Date and Time

Solved 
1K views 5 replies 2 participants last post by  Fireflycph 
#1 ·
Good day all,

I have an Access database app that is somewhat of a task tracker. There is now a need to track the task start date/time and the end date/time for the eventual purpose of showing how long the task took to complete.

[RecStartDate] is the date the record was created and signifies the task start date/time.
[Date Closed] is the date/time that the record/task is completed.

Ultimately, I'm seeking a method to run a query or report that would show the length of time in days, hours, and minutes for the retrieved records.

Before we get into this, I would like to consider the best methods for doing so. Would it be better to split date and time into two fields or is better that they stay in one? Would it be good to have a date picker so they can hit "Today" to capture current date and time or, since all they need is the current date/time, would it be better to have a button that inserts the date/time instead of a date picker?

Currently, [RecStartDate] is capturing the date and time in one field. This Access app is repurposed and it has a query that makes reference to the [RecStartDate]. When it was built, the date format was Short and didn't contain Time. I haven't tested the query yet but my hunch is that since I changed it to include the Time, there may be a problem with the query if the data in the field now contains the Time.

Secondarily, the [Date Closed] field has been problematic. I have set the property of the field to General Date. I can't get the field to record the date and time, only date. If I force it to Date() Time(), it will place the date and time in the field but it will do it on record creation and not when a user chooses. I need the user to either hit "Today" on a date picker or hit a button that enters the date and time into the field. This all is under the assumption that I should have both date and time together. Please correct me if it would be better to separate date and time.

I haven't gotten past this issue yet so I haven't begun attempting to build a query or report that will yield the needed results. I will definitely need help in that area as well.

Many thanks in advance.
 
See less See more
#3 ·
Fireflycph
The DB is split into front end and back end. I can send it to you but because the file paths wont be the same, you'll have to update the paths to the back end. The data that is in there is fake and only used for testing.

Update:
- I used a button instead of a date picker for the Date Closed field.
- I was able to get the closed date field to hold the date and time by using On click for the button, [Event Procedure], [Date Closed] = Date() + Time(). This seems to do the trick.

Now that the start and end dates are present, I'll need a query to handle the calculation from start date to end date. I'm seeking to have the total time represented as dd hh mm.

Fireflycph, I'm going to take another crack at it today but, should I get in over my head, would you be able to help with the query logic?
 
#5 ·
Fireflycph, I managed to get this project into a state that is acceptable to the users. Users wanted total hours per task.

Once I had the date and time stamp for the task start and task end, I needed to make the query that would provide the task length. I was able to achieve this using Task Length: Round(([RecStartDate]-[Date Closed])*24,2).

For those that this thread may help, the way this works is:
- [RecStartDate] is a date and time field that signifies the start of the task.
- [Date Closed] is a date and time field that signifies the task end.
- [RecStartDate]-[Date Closed] ...this means subtract [Date Closed] from [RecStartDate].
- [RecStartDate]-[Date Closed])*24 ...this means the answer from RecStartDate minus Date Closed is multiplied by 24. This yields a number that signifies hours and a fraction thereof after the decimal.
- Round(([RecStartDate]-[Date Closed])*24,2) The answers given contained too many numbers after the decimal so "Round" is used to round the number to two numbers after the decimal.
- Task Length is the column on the query in which the calculation resides and is also the field where the resulting answer will be shown.
- All together, the calculation string is: Task Length: Round(([RecStartDate]-[Date Closed])*24,2)

Fireflycph
, Thank you for the offer to help. It is much appreciated.
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top