Tags: access, beyond, certain, database, date, form, instances, microsoft, mysql, oracle, ortwo, sql, table, time, toinclude

Start time / stop time

On Database » Microsoft Access

5,354 words with 2 Comments; publish: Fri, 06 Jun 2008 09:03:00 GMT; (25078.00, « »)

Need a start time & stop time in a table and on a form, but need to

include date because in certain instances it may go beyond a day or

two. Seems to me the best way to do this is with a single date/time

field for start and stop. Problem is on the form I would like it user

friendly with date and time appearing to be separate fields and the

date automatically populating with current date. Probably don't even

want the date as a tab stop.

On the data entry form, I am guessing I would sum the date and time

entries then put it into the start time field. Any suggestions or

warnings on doing this?

On the data read/update form, split the date and time and allow for

updates similar to the data entry form. Any suggestions or warnings

on doing this?

Or would it be better to just break this down into four separate

fields? I am assuming two fields would be easier when getting around

to doing datediff (by time) functions.

Thanks for any suggestions.

All Comments

Leave a comment...

  • 2 Comments
    • You are correct: combining the date and time into a single field is much

      more efficient for most calculations.

      If this is for a single-view form (not Continuous or Datasheet view), you

      can use two unbound text boxes for the StartDateTime field. Use the Current

      event of the form to populate them:

      Private Sub Form_Current()

      If IsNull(Me.StartDateTime) Then

      Me.txtStartDate = Null

      Me.txtStartTime = Null

      Else

      Me.txtStartDate = DateValue(Me.StartDateTime)

      Me.txtStartTime = TimeValue(Me.StartDateTime)

      End If

      'Same for EndDateTime

      End Sub

      Use the AfterUpdate event of the text boxes to combine the value and assign

      to the field:

      Me.StartDateTime = Me.txtStartDate + Me.txtStartTime

      Use the Undo event of the form to reset the text boxes, based on the

      OldValue of the field.

      Allen Browne - Microsoft MVP. Perth, Western Australia.

      Tips for Access users - http://allenbrowne.com/tips.html

      Reply to group, rather than allenbrowne at mvps dot org.

      "Mark S" <na.ms-access.todaysummary.com.na.com> wrote in message

      news:82p781p8vk04pooi8rp28f45d7n0l53foi.ms-access.todaysummary.com.4ax.com...

      > Need a start time & stop time in a table and on a form, but need to

      > include date because in certain instances it may go beyond a day or

      > two. Seems to me the best way to do this is with a single date/time

      > field for start and stop. Problem is on the form I would like it user

      > friendly with date and time appearing to be separate fields and the

      > date automatically populating with current date. Probably don't even

      > want the date as a tab stop.

      > On the data entry form, I am guessing I would sum the date and time

      > entries then put it into the start time field. Any suggestions or

      > warnings on doing this?

      > On the data read/update form, split the date and time and allow for

      > updates similar to the data entry form. Any suggestions or warnings

      > on doing this?

      > Or would it be better to just break this down into four separate

      > fields? I am assuming two fields would be easier when getting around

      > to doing datediff (by time) functions.

      > Thanks for any suggestions.

      #1; Fri, 06 Jun 2008 09:04:00 GMT
    • Mark,

      If I understand your question correctly, you need a start time and a stop

      time. These times you want to show as just times ie: 11:57:45 PM (or

      whatever format). The times could span dates, so you want to include the

      date in the database field the date and time, but you do want the date shown

      in a different text box on the form. Now, one way would be to have two

      invisible controls - one for start time and one for stop time. These would

      be the bound fields. Then, you need four unbound text boxes - Start Date,

      Start Time, Stop Date, and Stop Time. You would then have to put some code

      in your form to fill in the visible fields (probably in the On Current for

      the form) and some to put the correct values in the invisible controls when

      you change values in the unbound controls (Maybe in the Form Before Update.)

      "Mark S" wrote:

      > Need a start time & stop time in a table and on a form, but need to

      > include date because in certain instances it may go beyond a day or

      > two. Seems to me the best way to do this is with a single date/time

      > field for start and stop. Problem is on the form I would like it user

      > friendly with date and time appearing to be separate fields and the

      > date automatically populating with current date. Probably don't even

      > want the date as a tab stop.

      > On the data entry form, I am guessing I would sum the date and time

      > entries then put it into the start time field. Any suggestions or

      > warnings on doing this?

      > On the data read/update form, split the date and time and allow for

      > updates similar to the data entry form. Any suggestions or warnings

      > on doing this?

      > Or would it be better to just break this down into four separate

      > fields? I am assuming two fields would be easier when getting around

      > to doing datediff (by time) functions.

      > Thanks for any suggestions.

      >

      #2; Fri, 06 Jun 2008 09:05:00 GMT