How do I display timestamp/timespan in Access 2007 form

[geeksatlarge]

I've created a table, in Access 2007, with the fields "ContactDate", FirstName, LastName, Start, End, and Duration. Date/Time is the Data Type for the fields "ContactDate", "Start", "End" and "Duration". I created a form that has Record Source set to the Table name and Recordset Type set to "Dynaset". The text boxes for ContactDate and Start have "Date()" and "Time()" as their Default Values, respectively. The date and time show in the form textboxes properly, and their values write to the table correctly. However, after a few days research and experimentation, I'm unable to get the form to show a timestamp for the "End" text box (the time when that record is inserted into the table), or a timespan value for the "Duration" text box (the time it took to create that record). I tried "=ElapedTimeString([Text4],[Text6])" and "DateDiff("hh,mm,ss",[Text4],[Text6])" as the Control Source for the "Duration" text box, but that didn't work. Can anyone shed light on where I'm going astray?

 

[DCrake]

When using the DateDiff function you can only return 1 interval type, such as days or hours or mins or secs. Thus DateDiff("hh,mm,ss",[Text4],[Text6])" is syntactically incorrect. Look up Access help on DateDiff()

 

[geeksatlarge]

Thanks David,

This time I tried '=DateDiff("n",Time(),[TimeStamps].[Text4])' as the value for the Control Source for The "Duration" field. Text4 being the "Duration" field. This didn't work either, but the text box displayed '#Name?' in form view.

 

[geeksatlarge]

There was a typo in my prior post. It should read:

This time I tried '=DateDiff("n",Time(),[TimeStamps].[Text4])' as the value for the Control Source for The "Duration" field. Text4 being the "Start" field. This didn't work either, but the text box displayed '#Name?' in form view.

 

[boblarson]

Thanks David,

This time I tried '=DateDiff("n",Time(),[TimeStamps].[Text4])' as the value for the Control Source for The "Duration" field. Text4 being the "Duration" field. This didn't work either, but the text box displayed '#Name?' in form view.

You don't use the text boxes you use the FIELD names.

And, you can't have the text box being the same name as one of the fields as it doesn't like that.

So you would actually have:

=DateDiff("n",[Start], [End])

But something else to note - Since you have the start time and end time you should NOT store the duration in the table. You calculate that at runtime.

 

[geeksatlarge]

Thanks boblarson,

I used '=DateDiff("s",Time(),[Start])' and got negative integers in the text box (Text6) that is associated with the field "Duration". You cleared a major source of frustration for me; really appreciated it. I used 'Time()' just to test with. I swapped their positions and got positive numbers, but what I'd really like to do is use the "End" field (text box Text4). How do I capture a timestamp, for that text box/field upon insert/moving on to a new record? I don't seem to be able to find that info anywhere.

 

[boblarson]

1. The earliest date/time comes first in the arguments just like I had them in my example code. End comes after Start so that is where they fall in the arguments.

2. To timestamp a record you would use the BeforeUpdate event of the FORM. So, when it goes to save the record you set the time stamp there. So, if all fields are filled out you can then use

Me.YourTextBoxNameHere = Now

does that help?

 

[geeksatlarge]

Yes, I did as you suggested and got "######..." in the textr box and table. So, I changed to the following:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.Text6 = Time
End Sub

That gave me the value 5:50:09 PM in the text box and in the table . That puts me "almost there". What was your train-of-though when you mentioned "Since you have the start time and end time you should NOT store the duration in the table. You calculate that at runtime"?

 

[boblarson]

In a normalized database, 99% of the time you do NOT store calculated values, especially when you have the parts stored that make up the calculation. In your case you have the start and the end stored so you don't store the duration. It is also for data integrity because if someone happned to change the value in the table itself you would not have a valid value for duration then.

Second, when you have ######## in the text box it normally means that the box is not wide enough to display the value.

Also, I store date and time together as it simplifies things (it gets to be a bit of a pain when you store them separately).

 

[geeksatlarge]

Thanks boblarson for your help.

My boss was having me enter similar information into a different Excel spreadsheet with additional headers. The fields I am using for this example are a cutdown version of that spreadsheet so I can solve for the most problematic cells (namely the date/time info).

Unfortunately, they want to maintain the format of the original spreadsheet, which has a "time duration" cell. My thinking was to at least make the entry of information easier for me by converting the Excel spreadsheet into an Access table and then create a form whereby I can more easily enter records. Entering repetitive data into an Excell spreadsheet, as you can imagine is error-prone.

I could then export the database back out to a spreadsheet whenever they need it.

I really appreciate your help on this. I'm leaving work for the day, but I'd appreciate your opinion on how best to go about calculating the difference in time for the "Duration" field. I already tried a couple of Event Procedures in Form After Update that didn't work out.

Thanks a lot.

 

[geeksatlarge]

Thanks to boblarson and DCrake my final solution was to was to use Date() as the Default Value for the "Date" text box, and Time() as the Default Value for the "StartTime" text box. Then use the following code in the Form's "Before Update" event (Thanks to: Brendan Reynolds, BerkshireGuy and MGFoster):

' display end time
Me.Text6 = Time
' declarations
Dim intHours As Integer
Dim intMinutes As Integer
Dim intSeconds As Integer
Dim HrsMinSecs As String
intSeconds = DateDiff("s", [Start], [End]) ' total seconds between time interval
intHours = intSeconds \ 3600 ' get how many hours that interval contains
intMinutes = intSeconds \ 60 Mod 60 ' how many minutes...
intSeconds = intSeconds Mod 60 ' how many seconds...
HrsMinSecs = CStr(intHours) & " Hr " & CStr(intMinutes) & " Min " & CStr(intSeconds) & " Sec "
Me.Text8 = HrsMinSecs ' display duration