date problem/grabbing data

  • Thread starter Thread starter RedStar
  • Start date Start date
R

RedStar

Hello. I am trying to make a music database.
First problem: I input minutes/seconds into a short time field...which is
really hours and minutes i guess.

how do i convert 4h:33 min into 4min:33 seconds without losing the data?
(if i change the time format to n:ss i get 43:0 instead of the desired
4:30.

And..is there an easy way to do the same thing media player does when loggin
music CDs?

ie if i stick in a music CD, how can i grab the track info without having to
manually input them?

TIA for any help :)
 
RedStar:

Check Datepart and Timeserial in Help.
hh=Datepart("h",ErroneousTime) will get the hour
mm=Datepart("m",ErroneousTime) will get the minute

Put it back as
ErroneousTime=Timeserial(0,hh,mm)
The variable ErroneousTime now has the hours in the
minute slot and the minutes in the seconds slot.

Ray
 
thanks for your response
i changed your answer to:

Function Correctime(Erroneoustime As Date)

hh = DatePart("h", Erroneoustime) 'will get the hour
Debug.Print hh
mm = DatePart("m", Erroneoustime) 'will get the minute
Debug.Print mm

'Put it back as
Erroneoustime = TimeSerial(0, hh, mm)
Correctime = Erroneoustime

but if i enter 4:31

i get 12 hrs 4 min 31 seconds.

the 0 in time serial = 12 am.

So obviously i am still not getting it

? TIA :)
 
RedStar: I assume your times are in a table. Open the
table in design view, move to the time field and under
General - Format insert nn:ss. This will instruct Access
to display only the minutes and seconds of the time. You
are seeing 12AM because your system time is set for a 12
hour clock. If you set the system time format for a 24
hour clock, the 12AM will go away.

To see it another way, in the Immediate Window of VB type
var = now
? var
? format(var,"nn:ss")

We must be getting close!

Ray
 
Hello. I am trying to make a music database.
First problem: I input minutes/seconds into a short time field...which is
really hours and minutes i guess.
Click to expand...

No, it's not. A Date/Time field is best for storing points in time,
NOT for storing durations; it's stored as a Double Float number, a
count of days and fractions of a day (times) since midnight, December
30, 1899.
how do i convert 4h:33 min into 4min:33 seconds without losing the data?
(if i change the time format to n:ss i get 43:0 instead of the desired
4:30.
Click to expand...

Divide by 60. 1 minute = 60 seconds!

So if you enter hours and minutes, you can divide by 60 to convert to
minutes and seconds.

However, you'ld be better off storing the duration of the track as a
Long Integer count of seconds; you can format it for display as nn:ss
using an expression like

[TrackTime] \ 60 & Format([TrackTime] MOD 60, ":00")
And..is there an easy way to do the same thing media player does when loggin
music CDs?
Click to expand...

Not easy, no; you'll need to write some VBA code and you'll need to
know a lot more than I do about the object model and program interface
of the CD music software!
 
Ok thanks for both of you answers.
I don't seem to be understanding how i should be storing this time data.

I used an update query to convert my date field to the right units by
dividing by 60...so now i get to enter time durations as as :04:31 to be
entered correctly.

But you say this is a bad way to store durations of time (and i do want to
be able to add the times up).

But if i convert the [timetrack] field to long integer or decimal --i fail.

and if i enter you formula anywhere it gets converted to:Format([timetrack]
Mod 60,":""00""") (extra quote marks) or i get type conversion fields.

So... I have a [timetrack] field as a time/date type formatted as nn:ss

so data looks like 12am:04m:33 s (displays as 04:31, and inputs as :04:31)

so what do i do now? 8-p

TIA :)

John Vinson said:
Hello. I am trying to make a music database.
First problem: I input minutes/seconds into a short time field...which is
really hours and minutes i guess.
Click to expand...

No, it's not. A Date/Time field is best for storing points in time,
NOT for storing durations; it's stored as a Double Float number, a
count of days and fractions of a day (times) since midnight, December
30, 1899.
how do i convert 4h:33 min into 4min:33 seconds without losing the data?
(if i change the time format to n:ss i get 43:0 instead of the desired
4:30.
Click to expand...

Divide by 60. 1 minute = 60 seconds!

So if you enter hours and minutes, you can divide by 60 to convert to
minutes and seconds.

However, you'ld be better off storing the duration of the track as a
Long Integer count of seconds; you can format it for display as nn:ss
using an expression like

[TrackTime] \ 60 & Format([TrackTime] MOD 60, ":00")
And..is there an easy way to do the same thing media player does when loggin
music CDs?
Click to expand...

Not easy, no; you'll need to write some VBA code and you'll need to
know a lot more than I do about the object model and program interface
of the CD music software!
Click to expand...
 
Ok thanks for both of you answers.
I don't seem to be understanding how i should be storing this time data.

I used an update query to convert my date field to the right units by
dividing by 60...so now i get to enter time durations as as :04:31 to be
entered correctly.

But you say this is a bad way to store durations of time (and i do want to
be able to add the times up).

But if i convert the [timetrack] field to long integer or decimal --i fail.

and if i enter you formula anywhere it gets converted to:Format([timetrack]
Mod 60,":""00""") (extra quote marks) or i get type conversion fields.

So... I have a [timetrack] field as a time/date type formatted as nn:ss

so data looks like 12am:04m:33 s (displays as 04:31, and inputs as :04:31)

so what do i do now? 8-p
Click to expand...

Reread my suggestion, for starters.

Durations *SHOULD NOT BE STORED IN DATE/TIME FIELDS*. Period.
You are seeing several of the reasons for this assertion.

Create a Long Integer field instead. If a track lasts 2 minutes 20
seconds, this field would contain 140 - a hundred and forty seconds.

My suggested formula was NOT a Format. It was an arithmetic expression
to convert a Long Integer number of seconds into the appearance (not
datatype, just looking like it) of nn:ss format.
 
Well, thats what i thought u ment at first..but the conversions failed
trying to go to long integer and then decimal so then i tried the other
solutions you may have ment. :)

so i'll just have to think on it some more..thanks for the reply.

over and out


John Vinson said:
Ok thanks for both of you answers.
I don't seem to be understanding how i should be storing this time data.

I used an update query to convert my date field to the right units by
dividing by 60...so now i get to enter time durations as as :04:31 to be
entered correctly.

But you say this is a bad way to store durations of time (and i do want to
be able to add the times up).

But if i convert the [timetrack] field to long integer or decimal --i fail.

and if i enter you formula anywhere it gets converted to:Format([timetrack]
Mod 60,":""00""") (extra quote marks) or i get type conversion fields.

So... I have a [timetrack] field as a time/date type formatted as nn:ss

so data looks like 12am:04m:33 s (displays as 04:31, and inputs as :04:31)

so what do i do now? 8-p
Click to expand...

Reread my suggestion, for starters.

Durations *SHOULD NOT BE STORED IN DATE/TIME FIELDS*. Period.
You are seeing several of the reasons for this assertion.

Create a Long Integer field instead. If a track lasts 2 minutes 20
seconds, this field would contain 140 - a hundred and forty seconds.

My suggested formula was NOT a Format. It was an arithmetic expression
to convert a Long Integer number of seconds into the appearance (not
datatype, just looking like it) of nn:ss format.
Click to expand...
 
Back
Top