Calculating separate date and time fields

[bupton]

I have separate date and time fields (necessary for what I am tracking). I am
using the following calculation: Hours:
(([FinalResponseDate]+[FinalRespTime])-([DateRequestedInfoReceived]+[TimeReqInfoRcvd]))*24

However, this is not calculating properly, I am getting different returns
for the same calculation: Requested Info Received date/time = 12/11/2007
5:59 pm Final Response date/time = 12/11/2007 5:59 pm will return -3.8666
for one record and 8.13333 for another, this should return 0.

I also do not want to include weekends and holidays - I already have the
module for this.

 

[Baz]

Sounds to me like you are heading for a world of pain. Much better to have
just one datetime field in the table for FinalResponseDatetime and one for
RequestedDateTime, and simply display them formatted as a date only or as a
time only as required.

 

[Douglas J. Steele]

Sorry, but even if you need to be able to refer to strictly the date or
strictly the time, you do not have to store them as separate fields. You can
always use the DateValue and/or TimeValue functions as computed fields in a
query, and use the query wherever you would otherwise have used the table.

Are you certain that your date values don't actually have times associated
with them?

 

[bupton]

I have to track them separately based on the actual date and time that an
e-mail has been received. Since the date and time is not the same as the db
entry it has to be separate. Same with the response, the db entry may occur
hours after the actual time the e-mail was responded to.

Sounds to me like you are heading for a world of pain. Much better to have
just one datetime field in the table for FinalResponseDatetime and one for
RequestedDateTime, and simply display them formatted as a date only or as a
time only as required.

I have separate date and time fields (necessary for what I am tracking). I
am
using the following calculation: Hours:
(([FinalResponseDate]+[FinalRespTime])-([DateRequestedInfoReceived]+[TimeReqInfoRcvd]))*24

However, this is not calculating properly, I am getting different returns
for the same calculation: Requested Info Received date/time = 12/11/2007
5:59 pm Final Response date/time = 12/11/2007 5:59 pm will return -3.8666
for one record and 8.13333 for another, this should return 0.

I also do not want to include weekends and holidays - I already have the
module for this.

 

[bupton]

Doug same response to you as BAZ - I have to track them separately based on
the actual date and time that an e-mail has been received. Since the date
and time is not the same as the db entry it has to be separate. Same with
the response, the db entry may occur hours after the actual time the e-mail
was responded to.

Sorry, but even if you need to be able to refer to strictly the date or
strictly the time, you do not have to store them as separate fields. You can
always use the DateValue and/or TimeValue functions as computed fields in a
query, and use the query wherever you would otherwise have used the table.

Are you certain that your date values don't actually have times associated
with them?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have separate date and time fields (necessary for what I am tracking). I
am
using the following calculation: Hours:
(([FinalResponseDate]+[FinalRespTime])-([DateRequestedInfoReceived]+[TimeReqInfoRcvd]))*24

However, this is not calculating properly, I am getting different returns
for the same calculation: Requested Info Received date/time = 12/11/2007
5:59 pm Final Response date/time = 12/11/2007 5:59 pm will return -3.8666
for one record and 8.13333 for another, this should return 0.

I also do not want to include weekends and holidays - I already have the
module for this.

 

[Rick Brandt]

I have to track them separately based on the actual date and time
that an e-mail has been received. Since the date and time is not the
same as the db entry it has to be separate. Same with the response,
the db entry may occur hours after the actual time the e-mail was
responded to.

So you have one DateTime field for when the record was created (if you need
that) and another DateTime field that holds the date AND time that the Email was
received.

 

[bupton]

I don't need to know when the record was created, I have a change tracking db
for that.

I have 4 fields, all date/time data types however 2 display manually entered
dates and the other 2 display manually entered times.

 

[Beetle]

What the other responders are trying to tell you is that you don't need to
store the date and time in two separate fields. You can enter any date and
time you want into one field ,as long as it isn't prior to midnite 1899 (i
think that's the cutoff if I remember correctly). In other words, you can
enter 12/01/07 9:30:00 AM into one field and Access will store it as a
numeric value that can then be used in your date calculation.

 

[Klatuu]

Sorry, bupton, but you are not paying attention. You have had some very good
advise on how it should be done. Wouldn't it occur to you that when several
posters offer the same advice that perhaps that is the correct approach.

I don't know how you plan to enter the dates and times, but you can still
use the one field in the table to show both date and time.
--
Dave Hargis, Microsoft Access MVP

Doug same response to you as BAZ - I have to track them separately based on
the actual date and time that an e-mail has been received. Since the date
and time is not the same as the db entry it has to be separate. Same with
the response, the db entry may occur hours after the actual time the e-mail
was responded to.

Sorry, but even if you need to be able to refer to strictly the date or
strictly the time, you do not have to store them as separate fields. You can
always use the DateValue and/or TimeValue functions as computed fields in a
query, and use the query wherever you would otherwise have used the table.

Are you certain that your date values don't actually have times associated
with them?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have separate date and time fields (necessary for what I am tracking). I
am
using the following calculation: Hours:
(([FinalResponseDate]+[FinalRespTime])-([DateRequestedInfoReceived]+[TimeReqInfoRcvd]))*24

However, this is not calculating properly, I am getting different returns
for the same calculation: Requested Info Received date/time = 12/11/2007
5:59 pm Final Response date/time = 12/11/2007 5:59 pm will return -3.8666
for one record and 8.13333 for another, this should return 0.

I also do not want to include weekends and holidays - I already have the
module for this.

 

[John W. Vinson]

What the other responders are trying to tell you is that you don't need to
store the date and time in two separate fields. You can enter any date and
time you want into one field ,as long as it isn't prior to midnite 1899 (i
think that's the cutoff if I remember correctly).

Actually any date/time between midnight, January 1, 100AD and 11:59:59pm
December 31, 9999 is permissible. The 0 value is in fact midnight, December
30, 1899 but negative numbers are handled correctly.

John W. Vinson [MVP]

 

[Jeanette Cunningham]

bupton,
have a field called ReceivedDateTime
have another field called RespondedDateTime

the first field will record both the date and time that the email was
received, you will be easily to write a query to get either the time it was
received, the date it was received or both the date and time it was
received.

the second field will do the same as the first one but it will do it for the
date and time that the email was responded to.

post back if you need some more explanation

Jeanette Cunningham

Doug same response to you as BAZ - I have to track them separately based
on
the actual date and time that an e-mail has been received. Since the date
and time is not the same as the db entry it has to be separate. Same with
the response, the db entry may occur hours after the actual time the
e-mail
was responded to.

Sorry, but even if you need to be able to refer to strictly the date or
strictly the time, you do not have to store them as separate fields. You
can
always use the DateValue and/or TimeValue functions as computed fields in
a
query, and use the query wherever you would otherwise have used the
table.

Are you certain that your date values don't actually have times
associated
with them?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have separate date and time fields (necessary for what I am tracking).
I
am
using the following calculation: Hours:
(([FinalResponseDate]+[FinalRespTime])-([DateRequestedInfoReceived]+[TimeReqInfoRcvd]))*24

However, this is not calculating properly, I am getting different
returns
for the same calculation: Requested Info Received date/time =
12/11/2007
5:59 pm Final Response date/time = 12/11/2007 5:59 pm will
return -3.8666
for one record and 8.13333 for another, this should return 0.

I also do not want to include weekends and holidays - I already have
the
module for this.

 

[Jeanette Cunningham]

bupton,
I can see the problem from a data entry point of view
You have the form open to enter data about when the email was received.
The email has both the date and time as 2 separate things.
Your difficulty is how to enter both the date and time into the same
textbox.

Have a play around with this.
Create a new table with only one field called DateReceived, set its data
type to DateTime, call it tblEmailReceived.
Create a new form with only one textbox. Set the form's data source to
tblEmailReceived.
Set the textbox's data source to DateReceived and its format property to
General Date.
In the text box type a date press spacebar once and enter the time
Access will display both the time and date in one textbox.
Type 24/12 space 10.56 am and see what you get.
Try a few other combinations until you get the hang of it.

Jeanette Cunningham

bupton,
have a field called ReceivedDateTime
have another field called RespondedDateTime

the first field will record both the date and time that the email was
received, you will be easily to write a query to get either the time it
was received, the date it was received or both the date and time it was
received.

the second field will do the same as the first one but it will do it for
the date and time that the email was responded to.

post back if you need some more explanation

Jeanette Cunningham

Doug same response to you as BAZ - I have to track them separately based
on
the actual date and time that an e-mail has been received. Since the
date
and time is not the same as the db entry it has to be separate. Same
with
the response, the db entry may occur hours after the actual time the
e-mail
was responded to.

Sorry, but even if you need to be able to refer to strictly the date or
strictly the time, you do not have to store them as separate fields. You
can
always use the DateValue and/or TimeValue functions as computed fields
in a
query, and use the query wherever you would otherwise have used the
table.

Are you certain that your date values don't actually have times
associated
with them?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have separate date and time fields (necessary for what I am
tracking). I
am
using the following calculation: Hours:
(([FinalResponseDate]+[FinalRespTime])-([DateRequestedInfoReceived]+[TimeReqInfoRcvd]))*24

However, this is not calculating properly, I am getting different
returns
for the same calculation: Requested Info Received date/time =
12/11/2007
5:59 pm Final Response date/time = 12/11/2007 5:59 pm will
return -3.8666
for one record and 8.13333 for another, this should return 0.

I also do not want to include weekends and holidays - I already have
the
module for this.

 

[bupton]

Jeanette,

THANK YOU for actually explaining to me what to do. I appreciate that
everyone suggested what I could do but if I don't know how to do what is
being suggested then I'm back to square 1.

bupton,
I can see the problem from a data entry point of view
You have the form open to enter data about when the email was received.
The email has both the date and time as 2 separate things.
Your difficulty is how to enter both the date and time into the same
textbox.

Have a play around with this.
Create a new table with only one field called DateReceived, set its data
type to DateTime, call it tblEmailReceived.
Create a new form with only one textbox. Set the form's data source to
tblEmailReceived.
Set the textbox's data source to DateReceived and its format property to
General Date.
In the text box type a date press spacebar once and enter the time
Access will display both the time and date in one textbox.
Type 24/12 space 10.56 am and see what you get.
Try a few other combinations until you get the hang of it.

Jeanette Cunningham

bupton,
have a field called ReceivedDateTime
have another field called RespondedDateTime

the first field will record both the date and time that the email was
received, you will be easily to write a query to get either the time it
was received, the date it was received or both the date and time it was
received.

the second field will do the same as the first one but it will do it for
the date and time that the email was responded to.

post back if you need some more explanation

Jeanette Cunningham

Doug same response to you as BAZ - I have to track them separately based
on
the actual date and time that an e-mail has been received. Since the
date
and time is not the same as the db entry it has to be separate. Same
with
the response, the db entry may occur hours after the actual time the
e-mail
was responded to.


:

Sorry, but even if you need to be able to refer to strictly the date or
strictly the time, you do not have to store them as separate fields. You
can
always use the DateValue and/or TimeValue functions as computed fields
in a
query, and use the query wherever you would otherwise have used the
table.

Are you certain that your date values don't actually have times
associated
with them?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have separate date and time fields (necessary for what I am
tracking). I
am
using the following calculation: Hours:
(([FinalResponseDate]+[FinalRespTime])-([DateRequestedInfoReceived]+[TimeReqInfoRcvd]))*24

However, this is not calculating properly, I am getting different
returns
for the same calculation: Requested Info Received date/time =
12/11/2007
5:59 pm Final Response date/time = 12/11/2007 5:59 pm will
return -3.8666
for one record and 8.13333 for another, this should return 0.

I also do not want to include weekends and holidays - I already have
the
module for this.

 

[bupton]

You're right, I wasn't getting it but giving me the advise and showing me the
advise makes all the difference in the world. It's like the secret family
recipe, you can tell someone it's easy to make but if they don't know what
goes into the recipe then they can't make it.

Sorry, bupton, but you are not paying attention. You have had some very good
advise on how it should be done. Wouldn't it occur to you that when several
posters offer the same advice that perhaps that is the correct approach.

I don't know how you plan to enter the dates and times, but you can still
use the one field in the table to show both date and time.
--
Dave Hargis, Microsoft Access MVP

Doug same response to you as BAZ - I have to track them separately based on
the actual date and time that an e-mail has been received. Since the date
and time is not the same as the db entry it has to be separate. Same with
the response, the db entry may occur hours after the actual time the e-mail
was responded to.

Sorry, but even if you need to be able to refer to strictly the date or
strictly the time, you do not have to store them as separate fields. You can
always use the DateValue and/or TimeValue functions as computed fields in a
query, and use the query wherever you would otherwise have used the table.

Are you certain that your date values don't actually have times associated
with them?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have separate date and time fields (necessary for what I am tracking). I
am
using the following calculation: Hours:
(([FinalResponseDate]+[FinalRespTime])-([DateRequestedInfoReceived]+[TimeReqInfoRcvd]))*24

However, this is not calculating properly, I am getting different returns
for the same calculation: Requested Info Received date/time = 12/11/2007
5:59 pm Final Response date/time = 12/11/2007 5:59 pm will return -3.8666
for one record and 8.13333 for another, this should return 0.

I also do not want to include weekends and holidays - I already have the
module for this.

 

[Klatuu]

Right you are. An old friend once said "It's easy if you know how".
Best of luck and Happy New Year.
--
Dave Hargis, Microsoft Access MVP

You're right, I wasn't getting it but giving me the advise and showing me the
advise makes all the difference in the world. It's like the secret family
recipe, you can tell someone it's easy to make but if they don't know what
goes into the recipe then they can't make it.

Sorry, bupton, but you are not paying attention. You have had some very good
advise on how it should be done. Wouldn't it occur to you that when several
posters offer the same advice that perhaps that is the correct approach.

I don't know how you plan to enter the dates and times, but you can still
use the one field in the table to show both date and time.
--
Dave Hargis, Microsoft Access MVP

Doug same response to you as BAZ - I have to track them separately based on
the actual date and time that an e-mail has been received. Since the date
and time is not the same as the db entry it has to be separate. Same with
the response, the db entry may occur hours after the actual time the e-mail
was responded to.


:

Sorry, but even if you need to be able to refer to strictly the date or
strictly the time, you do not have to store them as separate fields. You can
always use the DateValue and/or TimeValue functions as computed fields in a
query, and use the query wherever you would otherwise have used the table.

Are you certain that your date values don't actually have times associated
with them?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have separate date and time fields (necessary for what I am tracking). I
am
using the following calculation: Hours:
(([FinalResponseDate]+[FinalRespTime])-([DateRequestedInfoReceived]+[TimeReqInfoRcvd]))*24

However, this is not calculating properly, I am getting different returns
for the same calculation: Requested Info Received date/time = 12/11/2007
5:59 pm Final Response date/time = 12/11/2007 5:59 pm will return -3.8666
for one record and 8.13333 for another, this should return 0.

I also do not want to include weekends and holidays - I already have the
module for this.

 

[Jeanette Cunningham]

I am pleased that I could help you.

Jeanette Cunningham

Jeanette,

THANK YOU for actually explaining to me what to do. I appreciate that
everyone suggested what I could do but if I don't know how to do what is
being suggested then I'm back to square 1.

bupton,
I can see the problem from a data entry point of view
You have the form open to enter data about when the email was received.
The email has both the date and time as 2 separate things.
Your difficulty is how to enter both the date and time into the same
textbox.

Have a play around with this.
Create a new table with only one field called DateReceived, set its data
type to DateTime, call it tblEmailReceived.
Create a new form with only one textbox. Set the form's data source to
tblEmailReceived.
Set the textbox's data source to DateReceived and its format property to
General Date.
In the text box type a date press spacebar once and enter the time
Access will display both the time and date in one textbox.
Type 24/12 space 10.56 am and see what you get.
Try a few other combinations until you get the hang of it.

Jeanette Cunningham

bupton,
have a field called ReceivedDateTime
have another field called RespondedDateTime

the first field will record both the date and time that the email was
received, you will be easily to write a query to get either the time it
was received, the date it was received or both the date and time it was
received.

the second field will do the same as the first one but it will do it
for
the date and time that the email was responded to.

post back if you need some more explanation

Jeanette Cunningham



Doug same response to you as BAZ - I have to track them separately
based
on
the actual date and time that an e-mail has been received. Since the
date
and time is not the same as the db entry it has to be separate. Same
with
the response, the db entry may occur hours after the actual time the
e-mail
was responded to.


:

Sorry, but even if you need to be able to refer to strictly the date
or
strictly the time, you do not have to store them as separate fields.
You
can
always use the DateValue and/or TimeValue functions as computed
fields
in a
query, and use the query wherever you would otherwise have used the
table.

Are you certain that your date values don't actually have times
associated
with them?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have separate date and time fields (necessary for what I am
tracking). I
am
using the following calculation: Hours:
(([FinalResponseDate]+[FinalRespTime])-([DateRequestedInfoReceived]+[TimeReqInfoRcvd]))*24

However, this is not calculating properly, I am getting different
returns
for the same calculation: Requested Info Received date/time =
12/11/2007
5:59 pm Final Response date/time = 12/11/2007 5:59 pm will
return -3.8666
for one record and 8.13333 for another, this should return 0.

I also do not want to include weekends and holidays - I already
have
the
module for this.