Trying to convert a short time field from hours to minutes

[Kelly]

I need to convert a field that is in short time from hours and minutes to
minutes. I found this answer before but since redoing my database I have not
been able to find the answer back. Any help would be appreciated.

Thanks,
~Kelly

 

[Dale Fye]

Kelly,

If you are trying to store a duration (minutes) in a time field, then you
are using the wrong datatype. Date/Time fields are for storing just that,
dates and times.

If, on the other hand, you are storing time values, but only want to display
the minutes portion of the time (cannot imagine why you would want to do
this), then you need to use the Format function in the control source of a
textbox on your form. Something like:

ControlSource:=Format([TimeField], "nn")

Note that I've used 'n' not 'm' in this function. 'm' refers to month.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[John Spencer (MVP)]

If the field is a date time field then you should be able to convert it using

Hour([TheField])*60 + Minute([TheField])

or you can use

DateDiff("n",0,[TheField])

The first expression will ignore any date information in the field
The second expression will calculate the number of minutes between midnight on
Dec 30, 1899 and the time. If the time does not have a date, the datetime
field will automatically have a date of Dec 30, 1899.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Marshall Barton]

I need to convert a field that is in short time from hours and minutes to
minutes. I found this answer before but since redoing my database I have not
been able to find the answer back.

Short time is just a format and has nothing (or very liitle)
to do with the value of a field. You could format 1234.567
as Short Time or m/d/y if it would be useful.

If you have a value that is the difference of two Date/Time
fields, then it is stored in days and could also be
formatted as Short Time, but it is not really a time of day.
It's more like .567 days.

All that means that you can get the number of hours by
multiplying by 24 and the minutes would be the fractional
part of that. Multiply by 24*60 and you have the number of
minutes. All this can be done with one quick DateDiff("n",
....)

Once you have the number of minutes you can display them as
a string with hours and minutes by using an expression like:

Mins \ 60 & Format(Mins Mod 60, "\:00")

 

[Kelly]

My query does not like that expression. It keeps asking me for the time. I
am putting the expression under the criteria portion of the query and it
doesn't return anything. I am running a report off of this query and didn't
know if it would be easier to do this from the report or from the query.

If the field is a date time field then you should be able to convert it using

Hour([TheField])*60 + Minute([TheField])

or you can use

DateDiff("n",0,[TheField])

The first expression will ignore any date information in the field
The second expression will calculate the number of minutes between midnight on
Dec 30, 1899 and the time. If the time does not have a date, the datetime
field will automatically have a date of Dec 30, 1899.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I need to convert a field that is in short time from hours and minutes to
minutes. I found this answer before but since redoing my database I have not
been able to find the answer back. Any help would be appreciated.

Thanks,
~Kelly

 

[Kelly]

Okay, I'm sorry but this post was not helpful at all. What datatype should I
be using if Date/Time isn't right?

When I tried this before I was using this same datatype and it was working
just fine.

Kelly,

If you are trying to store a duration (minutes) in a time field, then you
are using the wrong datatype. Date/Time fields are for storing just that,
dates and times.

If, on the other hand, you are storing time values, but only want to display
the minutes portion of the time (cannot imagine why you would want to do
this), then you need to use the Format function in the control source of a
textbox on your form. Something like:

ControlSource:=Format([TimeField], "nn")

Note that I've used 'n' not 'm' in this function. 'm' refers to month.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

I need to convert a field that is in short time from hours and minutes to
minutes. I found this answer before but since redoing my database I have not
been able to find the answer back. Any help would be appreciated.

Thanks,
~Kelly

 

[Dale Fye]

Well, if you are trying to store a duration (how long it took to do
something), then I would recommend either an integer or long integer data
type. Then, decide what increment you want to use (hours, minutes, seconds),
and use the DateDiff function to determine the number of those increments
between two date/time values.

datediff("h", Time1, Time2)
datediff("n", Time1, Time2)
datediff("s", Time1, Time2)

Note: for minutes use 'n' instead of 'm', the 'm' represents months.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Okay, I'm sorry but this post was not helpful at all. What datatype should I
be using if Date/Time isn't right?

When I tried this before I was using this same datatype and it was working
just fine.

Kelly,

If you are trying to store a duration (minutes) in a time field, then you
are using the wrong datatype. Date/Time fields are for storing just that,
dates and times.

If, on the other hand, you are storing time values, but only want to display
the minutes portion of the time (cannot imagine why you would want to do
this), then you need to use the Format function in the control source of a
textbox on your form. Something like:

ControlSource:=Format([TimeField], "nn")

Note that I've used 'n' not 'm' in this function. 'm' refers to month.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

I need to convert a field that is in short time from hours and minutes to
minutes. I found this answer before but since redoing my database I have not
been able to find the answer back. Any help would be appreciated.

Thanks,
~Kelly

 

[John Spencer (MVP)]

That expression is not to be used in the criteria. It is to be used as a
calculated field.

Field: TheMinutes: Hour([YourFieldName])*60 + Minute([YourFieldName])

I assumed that your field is a dateTime field. A problem does arise if your
field is ever anything that cannot be interpreted as a time (null be an
exception to that problem)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

My query does not like that expression. It keeps asking me for the time. I
am putting the expression under the criteria portion of the query and it
doesn't return anything. I am running a report off of this query and didn't
know if it would be easier to do this from the report or from the query.

If the field is a date time field then you should be able to convert it using

Hour([TheField])*60 + Minute([TheField])

or you can use

DateDiff("n",0,[TheField])

The first expression will ignore any date information in the field
The second expression will calculate the number of minutes between midnight on
Dec 30, 1899 and the time. If the time does not have a date, the datetime
field will automatically have a date of Dec 30, 1899.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I need to convert a field that is in short time from hours and minutes to
minutes. I found this answer before but since redoing my database I have not
been able to find the answer back. Any help would be appreciated.

Thanks,
~Kelly

 

[John W. Vinson]

My query does not like that expression. It keeps asking me for the time. I
am putting the expression under the criteria portion of the query and it
doesn't return anything. I am running a report off of this query and didn't
know if it would be easier to do this from the report or from the query.

Put the expression in a blank Field cell as a calculated field - not in the
criteria.

 

[Ron2006]

Rule of thumb.

Time in Date/Time fields represents time of day

It does NOT mean the time difference between two events or two time
stamps.

Ron

 

[Kelly]

This is the expression that I put in my query and it's giving me weird results.
Expr1: Hour([Estimated Length]![Estimated Case Length]*60)+Minute([Estimated
Length]![Estimated Case Length])

Estimated Length is the field that contains hours and minutes.
When I type in 1:30 the expression above spits out 48
When I type in 00:20 it spits out 40

Anyone know what's going on?

 

[Dale Fye]

AS PREVIOUSLY INDICATED. You should not be storing an [estimated case
length] in a date/time field. You should be using an integer field and
storing minutes, or a decimal field and storing hours (where 1.5 = 90
minutes).

Converting either of these to an hh:mm format is relatively trivial, but
storing any type of elapsed time as a date/time field is flat WRONG!
--

Dale

email address is invalid
Please reply to newsgroup only.

This is the expression that I put in my query and it's giving me weird results.
Expr1: Hour([Estimated Length]![Estimated Case Length]*60)+Minute([Estimated
Length]![Estimated Case Length])

Estimated Length is the field that contains hours and minutes.
When I type in 1:30 the expression above spits out 48
When I type in 00:20 it spits out 40

Anyone know what's going on?

Put the expression in a blank Field cell as a calculated field - not in the
criteria.

 

[Kelly]

IT IS AN INTEGER! I'm getting this weird error message and would like some
help - not a message stating what I'm doing is wrong, tell me it's wrong but
then tell me how to fix IT!!!!!!!!!!!!!

AS PREVIOUSLY INDICATED. You should not be storing an [estimated case
length] in a date/time field. You should be using an integer field and
storing minutes, or a decimal field and storing hours (where 1.5 = 90
minutes).

Converting either of these to an hh:mm format is relatively trivial, but
storing any type of elapsed time as a date/time field is flat WRONG!
--

Dale

email address is invalid
Please reply to newsgroup only.

This is the expression that I put in my query and it's giving me weird results.
Expr1: Hour([Estimated Length]![Estimated Case Length]*60)+Minute([Estimated
Length]![Estimated Case Length])

Estimated Length is the field that contains hours and minutes.
When I type in 1:30 the expression above spits out 48
When I type in 00:20 it spits out 40

Anyone know what's going on?

My query does not like that expression. It keeps asking me for the time. I
am putting the expression under the criteria portion of the query and it
doesn't return anything. I am running a report off of this query and didn't
know if it would be easier to do this from the report or from the query.

Put the expression in a blank Field cell as a calculated field - not in the
criteria.

 

[Marshall Barton]

IT IS AN INTEGER! I'm getting this weird error message and would like some
help - not a message stating what I'm doing is wrong, tell me it's wrong but
then tell me how to fix IT!!!!!!!!!!!!!

This is the expression that I put in my query and it's giving me weird results.
Expr1: Hour([Estimated Length]![Estimated Case Length]*60)+Minute([Estimated
Length]![Estimated Case Length])

Estimated Length is the field that contains hours and minutes.
When I type in 1:30 the expression above spits out 48
When I type in 00:20 it spits out 40

If [Estimated Case Length] an Integer field, then it can not
have hours and minutes and the Hour and Minute functions
won't make sense.

The expression you're using only makes sense if [Estimated
Case Length] is a Date/Time field.

 

[Kelly]

I have figured this problem out FINALLY!!!!

The data type must be text. I have the Input Mask as Short Time so that
there is no AM or PM since this is a duration. Then in a query, where I want
to convert the text to minutes the formula is:

Your new column: CLng( 24 * 60 * Cdate( [ The data you want to convert ] ) )

Hope this helps anyone else having this same problem!!