show minutes not portions of hours in calculation

[Cheryl]

I'm working at showing Time Worked over a 24 hour period.
My expression calculates the correct amount of time, but
returns the minutes in a "portion of an hour" as a
repeating decimal, not the actual minutes. What do I need
to do to have it show the actual minutes instead of the
portions of an hour?

Hours Worked: IIf((DateDiff('n',[StartTime1],[StartTime2]))
<0,(DateDiff('n',[StartTime1],-[Endtime2]))+1440,DateDiff
('n',[StartTime1],[EndTime2]))/60

Thanks

 

[Ken Snell]

One example:

To get the display as hh:nn (hours:minutes):

HrMin: Format(DateDiff("n", StartDateTime, EndDateTime) \ 60, "0:") & _
Format(DateDiff("n", StartDateTime, EndDateTime) Mod 60, "00")

 

[Guest]

Hi Ken
Tried this and it tells me I have entered an operator
without an operand?

Thanks

-----Original Message-----
One example:

To get the display as hh:nn (hours:minutes):

HrMin: Format(DateDiff("n", StartDateTime, EndDateTime) \ 60, "0:") & _
Format(DateDiff("n", StartDateTime, EndDateTime) Mod 60, "00")


--
Ken Snell
<MS ACCESS MVP>

I'm working at showing Time Worked over a 24 hour period.
My expression calculates the correct amount of time, but
returns the minutes in a "portion of an hour" as a
repeating decimal, not the actual minutes. What do I need
to do to have it show the actual minutes instead of the
portions of an hour?

Hours Worked: IIf((DateDiff('n',[StartTime1], [StartTime2]))
<0,(DateDiff('n',[StartTime1],-[Endtime2])) +1440,DateDiff
('n',[StartTime1],[EndTime2]))/60

Thanks

.

 

[Ken Snell]

I had copied this from some code, and it appears that I left in the VBA
continuation character ( _ ) ! Sorry about that.

Try this (all one line, even though it'll wrap in the newsreader):

HrMin: Format(DateDiff("n", StartDateTime, EndDateTime) \ 60, "0:") &
Format(DateDiff("n", StartDateTime, EndDateTime) Mod 60, "00")


--
Ken Snell
<MS ACCESS MVP>

Hi Ken
Tried this and it tells me I have entered an operator
without an operand?

Thanks

-----Original Message-----
One example:

To get the display as hh:nn (hours:minutes):

HrMin: Format(DateDiff("n", StartDateTime, EndDateTime) \ 60, "0:") & _
Format(DateDiff("n", StartDateTime, EndDateTime) Mod 60, "00")


--
Ken Snell
<MS ACCESS MVP>

I'm working at showing Time Worked over a 24 hour period.
My expression calculates the correct amount of time, but
returns the minutes in a "portion of an hour" as a
repeating decimal, not the actual minutes. What do I need
to do to have it show the actual minutes instead of the
portions of an hour?

Hours Worked: IIf((DateDiff('n',[StartTime1], [StartTime2]))
<0,(DateDiff('n',[StartTime1],-[Endtime2])) +1440,DateDiff
('n',[StartTime1],[EndTime2]))/60

Thanks

.

 

[Guest]

Thanks Ken, that was the problem. I did try removing
that, but must have removed something else in the
process.
One small problem now - it's not showing the total hours
worked, but only the portion minus 24 hours. These shifts
run over a 24 hour period. I need to insert "1440" in the
equation somewhere to achieve the correct answer, just now
sure where, or how much to insert.

Ken, do you know of a good book (or internet site) that
can teach a person how to build these expressions? I've
been searching for quite some time, and can't seem to find
anything that teaches about structuring them from the
ground up - or explaining why or how you structure the
expression to acheive expected results. I've come across
a lot of information on VBA, but not too much on creating
expressions for Access. I want to learn how to do this
without requiring so much help!!! (Help which I'm very
thankful for!!)

Sincerely
Cheryl

-----Original Message-----
I had copied this from some code, and it appears that I left in the VBA
continuation character ( _ ) ! Sorry about that.

Try this (all one line, even though it'll wrap in the newsreader):

HrMin: Format(DateDiff("n", StartDateTime, EndDateTime) \ 60, "0:") &
Format(DateDiff("n", StartDateTime, EndDateTime) Mod 60, "00")


--
Ken Snell
<MS ACCESS MVP>

Hi Ken
Tried this and it tells me I have entered an operator
without an operand?

Thanks

-----Original Message-----
One example:

To get the display as hh:nn (hours:minutes):

HrMin: Format(DateDiff("n", StartDateTime,

EndDateTime) \
60, "0:") & _

Format(DateDiff("n", StartDateTime, EndDateTime)

Mod
60, "00")

--
Ken Snell
<MS ACCESS MVP>

I'm working at showing Time Worked over a 24 hour period.
My expression calculates the correct amount of time, but
returns the minutes in a "portion of an hour" as a
repeating decimal, not the actual minutes. What do I need
to do to have it show the actual minutes instead of the
portions of an hour?

Hours Worked: IIf((DateDiff('n',[StartTime1], [StartTime2]))

<0,(DateDiff('n',[StartTime1],-[Endtime2]))

+1440,DateDiff
('n',[StartTime1],[EndTime2]))/60

Thanks


.

.

 

[Ken Snell]

You must be storing the time and date separately? It'll work better if you
store them in the same field, and then the expression that I provided to you
will work just fine. If you're letting users enter the date and time
separately, you can combine them via code in your entry form and then store
them into the single field:
DateTimeFieldValue = DateFieldValue + TimeFieldValue

If you must have them be separate, then combine them in the example that I
gave to you (again, all one line):

HrMin: Format(DateDiff("n", StartDate + StartTime, EndDate + EndTime) \ 60,
"0:") & Format(DateDiff("n", StartDate + StartTime, EndDate + EndTime) Mod
60, "00")

With respect to a book, most any book on VBA for ACCESS will have info on
these setups in a basic way....otherwise, quite honestly, I've learned them
by reading Help file, reading newsgroup postings (www.google.com allows you
to search old newsgroup posts -- choose the Groups option), and by asking
questions here.


--
Ken Snell
<MS ACCESS MVP>

Thanks Ken, that was the problem. I did try removing
that, but must have removed something else in the
process.
One small problem now - it's not showing the total hours
worked, but only the portion minus 24 hours. These shifts
run over a 24 hour period. I need to insert "1440" in the
equation somewhere to achieve the correct answer, just now
sure where, or how much to insert.

Ken, do you know of a good book (or internet site) that
can teach a person how to build these expressions? I've
been searching for quite some time, and can't seem to find
anything that teaches about structuring them from the
ground up - or explaining why or how you structure the
expression to acheive expected results. I've come across
a lot of information on VBA, but not too much on creating
expressions for Access. I want to learn how to do this
without requiring so much help!!! (Help which I'm very
thankful for!!)

Sincerely
Cheryl

-----Original Message-----
I had copied this from some code, and it appears that I left in the VBA
continuation character ( _ ) ! Sorry about that.

Try this (all one line, even though it'll wrap in the newsreader):

HrMin: Format(DateDiff("n", StartDateTime, EndDateTime) \ 60, "0:") &
Format(DateDiff("n", StartDateTime, EndDateTime) Mod 60, "00")


--
Ken Snell
<MS ACCESS MVP>

Hi Ken
Tried this and it tells me I have entered an operator
without an operand?

Thanks
-----Original Message-----
One example:

To get the display as hh:nn (hours:minutes):

HrMin: Format(DateDiff("n", StartDateTime, EndDateTime) \
60, "0:") & _
Format(DateDiff("n", StartDateTime, EndDateTime) Mod
60, "00")


--
Ken Snell
<MS ACCESS MVP>

message
I'm working at showing Time Worked over a 24 hour
period.
My expression calculates the correct amount of time, but
returns the minutes in a "portion of an hour" as a
repeating decimal, not the actual minutes. What do I
need
to do to have it show the actual minutes instead of the
portions of an hour?

Hours Worked: IIf((DateDiff('n',[StartTime1],
[StartTime2]))
<0,(DateDiff('n',[StartTime1],-[Endtime2]))
+1440,DateDiff
('n',[StartTime1],[EndTime2]))/60

Thanks


.

.

 

[Guest]

This has resulted in the answer I was looking for! I have
the date formatted as "General Date", however they will be
entering date and time seperately. My error was in not
paying attention to the exact info required, and I was
inputting time all the way across instead of "date and
time" in the appropriate fields.

I'll check out the google site too, and will no doubt be
posting back again when I hit a snag.

Thanks for all your help.
Cheryl

-----Original Message-----
You must be storing the time and date separately? It'll work better if you
store them in the same field, and then the expression that I provided to you
will work just fine. If you're letting users enter the date and time
separately, you can combine them via code in your entry form and then store
them into the single field:
DateTimeFieldValue = DateFieldValue + TimeFieldValue

If you must have them be separate, then combine them in the example that I
gave to you (again, all one line):

HrMin: Format(DateDiff("n", StartDate + StartTime, EndDate + EndTime) \ 60,
"0:") & Format(DateDiff("n", StartDate + StartTime, EndDate + EndTime) Mod
60, "00")

With respect to a book, most any book on VBA for ACCESS will have info on
these setups in a basic way....otherwise, quite honestly, I've learned them
by reading Help file, reading newsgroup postings

(www.google.com allows you

to search old newsgroup posts -- choose the Groups option), and by asking
questions here.


--
Ken Snell
<MS ACCESS MVP>

Thanks Ken, that was the problem. I did try removing
that, but must have removed something else in the
process.
One small problem now - it's not showing the total hours
worked, but only the portion minus 24 hours. These shifts
run over a 24 hour period. I need to insert "1440" in the
equation somewhere to achieve the correct answer, just now
sure where, or how much to insert.

Ken, do you know of a good book (or internet site) that
can teach a person how to build these expressions? I've
been searching for quite some time, and can't seem to find
anything that teaches about structuring them from the
ground up - or explaining why or how you structure the
expression to acheive expected results. I've come across
a lot of information on VBA, but not too much on creating
expressions for Access. I want to learn how to do this
without requiring so much help!!! (Help which I'm very
thankful for!!)

Sincerely
Cheryl

-----Original Message-----
I had copied this from some code, and it appears that I left in the VBA
continuation character ( _ ) ! Sorry about that.

Try this (all one line, even though it'll wrap in the newsreader):

HrMin: Format(DateDiff("n", StartDateTime,

EndDateTime) \
60, "0:") &

Format(DateDiff("n", StartDateTime, EndDateTime) Mod 60, "00")


--
Ken Snell
<MS ACCESS MVP>

Hi Ken
Tried this and it tells me I have entered an operator
without an operand?

Thanks
-----Original Message-----
One example:

To get the display as hh:nn (hours:minutes):

HrMin: Format(DateDiff("n", StartDateTime, EndDateTime) \
60, "0:") & _
Format(DateDiff("n", StartDateTime, EndDateTime) Mod
60, "00")


--
Ken Snell
<MS ACCESS MVP>

message
I'm working at showing Time Worked over a 24 hour
period.
My expression calculates the correct amount of

time,
but

returns the minutes in a "portion of an hour" as a
repeating decimal, not the actual minutes. What do I
need
to do to have it show the actual minutes instead

of
the

portions of an hour?

Hours Worked: IIf((DateDiff('n',[StartTime1],
[StartTime2]))
<0,(DateDiff('n',[StartTime1],-[Endtime2]))
+1440,DateDiff
('n',[StartTime1],[EndTime2]))/60

Thanks


.



.

.