0

I'm creating an Access database from an excel file that contains metadata about vhs tapes and DVDs. The person who created the excel file had the running times as a date/time field in minutes:seconds. I would like to be able to create reports or set up forms/queries etc. where the user can search for minutes greater than such and such. I was thinking that the best way would be to create two fields: minutes and seconds. The date/time type is really for specifying chronological times, not running times.

What is the best way to do this? How can I split the minutes:seconds to create two columns?

I only just imported the Excel table into Access. Would it be easier to do it as a formula in Excel and then import to Access?

1

Store the running time as an integer in total seconds. For display purposes you can convert the number of seconds into a string in hours:minutes:seconds format or whatever display format you choose.

0

The DatePart function will extract a specified part of a date/time field.

The following example extracts the hour from the current time.

=DatePart("h", Now()) 

Here is a link with more information on the function.

  • Is it appropriate to use the Date type for the running time of a video? Seems not. Don't you think I should use a different data type? – abalter Aug 5 '14 at 23:01
  • My apologies; I misread your question. You probably shouldn't use two columns that represent the total time. The best option would be to use one column with a Double datatype where the decimal would represent the seconds. It's not ideal, but I don't know of a better way to represent time in Access. – Weston Sankey Aug 6 '14 at 1:06
  • Ok. Thanks. Between the two, I think I will take your first suggestion. Using the Date data type at least has a spot for minutes and seconds, and is less likely to be confusing. – abalter Aug 6 '14 at 14:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.