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-28T11:42:48+00:00

    What you need to understand is how Access deals with DateTime values. They are considered to be a double precision number where the integer portion is the number of days since 12/30/1899 and the decimal portion is a fraction of a day (i.e. .25 = 6AM).

    The point that Ken makes about the expression not working above 3599 seconds is because there are 3600 seconds in a day. so if you exceed 3599 the value when converted to a DateTime value would be 1.0.

    To use the expressions Daniel, Duane and Ken have given you, you use the expression as the ControlSource of a control on a Form or Report or as a calculated column in a query.

    0 comments
  2. Anonymous
    2025-05-28T12:06:43+00:00

    Firstly, you cannot format the column in the table's datasheet. That should show the actual value in seconds. Similarly in a bound form, to enter or edit data in the form the text box control should be bound to the column, and unformatted.

    To show the formatted value in a form or report, set the ControlSource property of an unbound control to the expression, e.g.

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

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

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

    The formatted values in a form or the result table of a query will be read-only of course.

    However, another way to handle this type of data would be to add a new column of DateTime data type to the table, naming it Dur1Formatted say. Then execute an update query to update the new column to:

    [Dur1]/60/60/24

    Once you are happy that the new Dur1Formatted column has been populated correctly you can drop the original Dur1 column from the table and rename the new column as Dur1. You'd then be able to enter/edit the values in hh:nn:ss format. Set the column's Format property to hh:nn:ss.

    You could also do time arithmetic on the column if necessary, Note that any results of 24 hours or more could not be formatted as hh:nn:ss. For examples of functions to handle such values and return the results formatted correctly take a look at TimeArithmetic.zip in my Dropbox public databases folder at:

    https://www.dropbox.com/scl/fo/0scigd3r48hx5xrev2jrf/AB0-GMdTgMAO5O1cGdr3QW0?rlkey=ib6bs6g9jqcrywwzivur3265t&dl=0

    0 comments
  3. Anonymous
    2025-05-28T12:35:58+00:00

    Scott, on the ControlSource property sheet I only have a General Tab. I am understanding there should be a Data Tab to enter the expression.

    0 comments
  4. Anonymous
    2025-05-28T14:37:49+00:00

    Your image shows a table in design view. The ControlSource property is a property of a control, e.g. a text box, combo box etc. in a form or report. A control can be bound, in which case the property is the name of a column in a table or query to which the form is bound, or it can be unbound, in which case its ControlSource property is an expression which returns a value, or the property is left empty, in which case a value is assigned to it at runtime, e.g. by the user entering the value, or by a value being inserted by VBA code or a macro.

    0 comments