Share via

Access Database Question

Anonymous
2025-05-27T23:36:46+00:00

Hello, I need some help with a database that is many years old. I have two duration fields, named Dur1 and Dur2 which display the time of some recordings that I have. The problem is they are formatted as numerical and the entries are in seconds, so that 140 equals two minutes twenty seconds. I would like to have them appear as 02:20 instead, that might be formatted as nn:ss

I've done a lot of browsing and much of what I am seeing I am not familiar with, formulas, expressions and syntax.

I am just looking to have someone assist in a clearcut approach to have these two fields corrected.

Thank you for any assistance.

Microsoft 365 and Office | Access | For home | Other

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Accepted answer
  1. Anonymous
    2025-05-29T17:02:44+00:00

    I have Dur1Formatted and Dur2Formatted working in a new Query, but I still have the Dur1 and Dur2 fields & am not certain they can be deleted or not.

    Also, is it possible to add Dur1Formatted and Dur2Formatted to my Table?

    You must not drop the Dur1 and Dur2 columns from the table. They hold the data. The formatted computed columns are just a different way of looking at that data. You might be able to add 'calculated fields' to the table using the same expressions, but why would you want to? The place for returning computed values is in a query in my view.

    You can rename the computed columns to whatever you want, but if you want to use the original column names, the column names must be qualified with the table name in the expressions.

    0 comments

15 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2025-05-28T01:04:41+00:00

    Perhaps try an expression such as

    =Format(Int([Dur1]/60),"00") & ":" & Format([Dur1] Mod 60,"00")

    Repeat for Dur2

    You could also turn this into a public function and call it as required.

    0 comments
  2. Anonymous
    2025-05-28T01:25:23+00:00

    If the value is in seconds, I would divide the value by the number of seconds in a day. Then format using a time format.

    0 comments
  3. Anonymous
    2025-05-28T10:53:43+00:00

    Store the values in seconds as at present, and format them for output with:

    Format(Dur1/60/60/24,"nn:ss")

    Note that this will not cope with values above 3599 seconds. If any such values are possible then you should parse the value with Daniel's expression. If you do any arithmetic on the values, this should be done on the original values, and the formatting or parsing then applied to the result.

    0 comments
  4. Anonymous
    2025-05-28T11:07:09+00:00

    Ken, Thank you for your reply. How do I go about formatting? I have been trying to do this in Design View, and there is also a drop down for the particular data field for such as number, short text, etc. Am I in the right area to enter the expression?

    0 comments