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-28T21:33:59+00:00

    What I said was "ControlSource of a control on a Form or Report or as a calculated column in a query" As Ken said, you were looking at the table in Design Mode, Not a form or report.

    Tables are for storing data, you use queries, forms and reports to display data. On a form or report you do NOT have fields, you have Controls that may or may not be bound to fields in a table. This is a subtle but important distinction.

    Another point. Looking at your table, you seem to be dealing record tracks on a LP or CD. In that case you have a problem with table structure. You have fields named Track1, Track2, etc. This is not good design. Your main table should be a Tracks table where each record is a single track. Albums usually have more than 4 tracks. You would then have an Albums table with details about the album, The Tracks table would then have a foreign key relating it back to the Album record. You might want to do a search on music library database schema to find examples of this type of database.

    0 comments
  2. Anonymous
    2025-05-28T22:21:55+00:00

    Scott,

    This database was custom designed by an outside vendor many years ago, I assume with Access 97 and I had converted it to .ACCDB. His program no longer functions but my database containing 1100 45 rpm entries, which took months to complete is what I have remaining. It would be impractical to start over.

    With regard to some entries with three or four tracks, some 45 rpm records are extended play.

    I am just trying to improve what I have but I am not well versed with Access, but lucky enough to get the database semi-working. Again, not my design.

    If someone would be willing to make my database more user friendly, I can forward it for fine tuning and I would be most appreciative.

    0 comments
  3. Anonymous
    2025-05-29T13:47:43+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.

    0 comments
  4. Anonymous
    2025-05-29T14:15:54+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.

    Again, you can add a calculated column to a query or use the expression as the Controlsource for a control on a Form.

    As to the structure. I understand the point about this being 45s which generally have an A and B side.

    0 comments