Need to truncate a text field that contains time duration

[ultrarunner2017]

Hi;


I use an Access 365 database to catalog my music library. When I created the db, I mistakenly used a date/time field for the track duration, then converted it to a text field. Doing that, I ended up with the format hh:nn:ss (where nn is minutes). When I entered the times for each track, I used only the hh:nn but not the ss, so all the records show 00 for seconds.
After entering quite a bit of data, I changed the input mask for the time field to 00:00.
I am looking for a method - probably an update query - to change all the times to use only hh:nn. I thought since it's a text field, I should be able to use the len function to truncate the field to 5 characters (from 8 characters), but it's been a while since I've done any 'coding' for Access.

Thanks for your help
Ultrarunner

[Micron]

There are always leading zeros when values are less than 10, as in 01:05:00?
Then perhaps UPDATE tblSongTracks SET [Duration] = Left([Duration],5)

Do that on a copy of your table first. Substitute your own table and field names. This is a common topic that would have countless examples of how to do it already posted and would save you from waiting around for an answer.

[ultrarunner2017]

Thanks; That's what I was thinking (to use the Left operator) but wasn't sure about the syntax. I guess I need to update my Access 'expertise'. I don't do anything professional - only for my own use, but I enjoy working with Access, and VBA - although, I've not done anything for several years.

[ultrarunner2017]

After some trial and error, using the update query, I got the result I was expecting. Good thing I took your advice and did the backup!

Now, all I need to do is figure out a way to copy all the records (data only) from one table into another, as I am in the process of rebuilding the database, and would like to avoid having to copy and paste the data manually.
Reason I needed to rebuild the db is that I eschewed all of the good advice on building a database, and used one of the data fields (not an auto-number field) as my primary key. I don't know why I did it that way, after all the advice I have read on that subject, and why it is a terrible mistake to do so.
In any case, I needed to truncate the trk_time field to 5 characters so that it would match the field in the new table; otherwise I get paste errors.

Sometimes though, I figure it's faster and easier to do the task manually, rather than build code. But I had a feeling that truncating the text field would be an easy task, and I need to learn how to use Access properly anyway!

[Micron]

Now, all I need to do is figure out a way to copy all the records (data only) from one table into another,

Append query?
You might be in for a bit of work trying to match related records with the new pk values (I presume they're autonumber id's) on the "one" side of the many.

[ultrarunner2017]

Since this is a one-time project, I guess it's not such a big deal to do it manually, unless I really want to jump (far) ahead and start writing code; but considering how much of Access I still need to learn, I don't think writing code is something I should be getting into just yet.
I thought perhaps I could simply copy all of the records from the old 'songs' table into the new table, but because the relationships are different between the two databases, I only get errors when I try to do that.
But I can copy and paste by descending from the artists table to the albums and then copy an entire album at once. Before I start any new Access projects, I'll do some studying, so that I have a better understanding of how a relational database works.

Since I now have a subscription to MS 365, I should be able to get some decent training materials without additional charge - I will take a look at Access video training.