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-29T15:03:44+00:00

    Let's try this with a calculated column, Dur1 Formatted. Can you provide step by step instructions, I assume this will be in a form of a query? I think I am missing a key detail somewhere.

    To return a computed column in a query's result table, in query design view you'd do as I said in my first reply, viz:

    *In a query, in the Field row of a blank column in the design grid enter:*

*Dur1Formatted: Format([Dur1]/60/60/24,"nn:ss")*

    This would translate in the query's SQL statement to the following in the SELECT clause:

    Format([Dur1]/60/60/24,"nn:ss") AS Dur1Formatted

    You can then bind a text box in the form to the computed Dur1Formatted column. However, the column, and hence the text box, would be read-only. My preference would be to add a new column of DateTime data type to the table, and insert the values from the current column with an UPDATE query, as described in the second part of my earlier reply. Then, not only would you see the value in time format, you'd be able to edit it in that format. When entering a value manually you would need to include the zero hours, however, or the minutes would be read as hours. You can see this if you enter a literal time value in the immediate window:

    ? #08:10#
    08:10:00

    whereas:

    ? #00:08:10#

    00:08:10

    0 comments
  2. Anonymous
    2025-05-29T16:38:33+00:00

    Ken,

    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?

    Lastly, can these fields be renamed ?

    Thank you!

    0 comments
  3. Anonymous
    2025-05-29T17:27:06+00:00

    Ken,

    I hid Dur1 & Dur2. I then renamed Dur1Format & Dur2Format in the expression builder.

    I will leave the table as is and query the database as needed.

    Many Thanks!

    Chet

    0 comments