When return is over 24 hours.

[youkr]

I am only new to using access and am not too savy so please be kind...
I am trying to determine the difference in time between an admission and
discharge time. I have seached the other posts, but dont seem to find the
answer (or one I understand). The time and dates are entered in different
fields on the database.The query I am currently using is:

"LOS (Hours:Mins)":
(EDMain!EDDischargeDate+EDMain!EDDischargeTime)-(EDMain!EDPresentationDate+EDMain!EDPresentationTime)

The return only show hours and minutes (23:20), if the hours are over 24 it
leaves off the days, or wont count up over 24 hours i.e. it shows 26 hours 30
mins as 2:30. I dont really mind how it shows over 24 hours i.e 26:30, or
1:2:30.

Any help, would be great. Thankyou in advance.

 

[John W. Vinson]

I am only new to using access and am not too savy so please be kind...
I am trying to determine the difference in time between an admission and
discharge time. I have seached the other posts, but dont seem to find the
answer (or one I understand). The time and dates are entered in different
fields on the database.The query I am currently using is:

"LOS (Hours:Mins)":
(EDMain!EDDischargeDate+EDMain!EDDischargeTime)-(EDMain!EDPresentationDate+EDMain!EDPresentationTime)

The return only show hours and minutes (23:20), if the hours are over 24 it
leaves off the days, or wont count up over 24 hours i.e. it shows 26 hours 30
mins as 2:30. I dont really mind how it shows over 24 hours i.e 26:30, or
1:2:30.

Any help, would be great. Thankyou in advance.

The problem is that an Access date/time value is designed to store a specific
point in time. It's stored internally as a Double Float number, a count of
days and fractions of a day since midnight, December 30, 1899; so 26:30 is
actually stored as 1.104166666666666666 and corresponds to #12/31/1899 02:30#.

Rather than subtracting date/time values, consider using the DateDiff function
to calculate the time in minutes. You may also want to simplify your life by
storing the date and time in the same field. You can display a duration in
minutes in hh:nn format with an expression like

[Duration] \ 60 & [Duration] MOD 60, "\:00")

 

[youkr]

Thanks John,

I have had a go with the date diff function (and the date2diff functions
mentioned on the forum), however end up with #Error. THis is the query I have
written:

LOS1:
(DateDiff("hn",([EDMain!EDDischargeDate]+[EDMain!EDDischargeTime]),([EDMain!EDPresentationDate]+[EDMain!EDPresentationTime]))


Any ideas as to what might be wrong? I tried to change the date/time fields
to be in the same field, however this did nto go so well!!!

thanks for your suggestions.

I am only new to using access and am not too savy so please be kind...
I am trying to determine the difference in time between an admission and
discharge time. I have seached the other posts, but dont seem to find the
answer (or one I understand). The time and dates are entered in different
fields on the database.The query I am currently using is:

"LOS (Hours:Mins)":
(EDMain!EDDischargeDate+EDMain!EDDischargeTime)-(EDMain!EDPresentationDate+EDMain!EDPresentationTime)

The return only show hours and minutes (23:20), if the hours are over 24 it
leaves off the days, or wont count up over 24 hours i.e. it shows 26 hours 30
mins as 2:30. I dont really mind how it shows over 24 hours i.e 26:30, or
1:2:30.

Any help, would be great. Thankyou in advance.

The problem is that an Access date/time value is designed to store a specific
point in time. It's stored internally as a Double Float number, a count of
days and fractions of a day since midnight, December 30, 1899; so 26:30 is
actually stored as 1.104166666666666666 and corresponds to #12/31/1899 02:30#.

Rather than subtracting date/time values, consider using the DateDiff function
to calculate the time in minutes. You may also want to simplify your life by
storing the date and time in the same field. You can display a duration in
minutes in hh:nn format with an expression like

[Duration] \ 60 & [Duration] MOD 60, "\:00")

 

[John W. Vinson]

Thanks John,

I have had a go with the date diff function (and the date2diff functions
mentioned on the forum), however end up with #Error. THis is the query I have
written:

LOS1:
(DateDiff("hn",([EDMain!EDDischargeDate]+[EDMain!EDDischargeTime]),([EDMain!EDPresentationDate]+[EDMain!EDPresentationTime]))

"hn" is a valid operand for Date2Diff, but it is NOT for DateDiff. Either
change it to "n" to get total minutes, or use Date2Diff (once you've included
Date2Diff in your database, it's not a builtin function).

Any ideas as to what might be wrong? I tried to change the date/time fields
to be in the same field, however this did nto go so well!!!

What went wrong? You'ld clearly need to change your data entry forms and
reports (I presume they have two textboxes, for date and time respectively;
this would need to be changed).

 

[youkr]

John,
Thanks for your time.

I have not included the Diff2Dates funciton in my database, so this is
probably why it didnt work! I have had a look at the the posts and the help
funciton about how to include the new funciton, but am a little unsure how to
do this - the whole database was developed by someone else who has since left
my organsieation - there is no one else who can help - the IT department have
not idea about access - I knew more about it than them, and that is saying
something! I somehow changed the settings and lost access to over 1000 data
entries yesterday trying to consolidate the time and date fields to be the
same ones, given I thought that would be a relatively simple exercise I am
now hesitant to press save for anything!

Could you please let me know how to include the diff2dates funciton - this
seems to be the best option for my issue, as I need to show days, minutes and
hours. I can get to a point where I cut and past the fucntion onto a new
module, but I am not sure what to do from here - I havent saved this yet, if
I save the new module will it affect any of the other items/functions on the
database, or is it just stored there until it is called upon when I enter the
Diff2date in the query section? Other than cut and past, and then save and
name the module - do I have to make any alterations/changes to it? I have
cutt and paste it from a link from the forum.
Thanks for your help

Thanks John,

I have had a go with the date diff function (and the date2diff functions
mentioned on the forum), however end up with #Error. THis is the query I have
written:

LOS1:
(DateDiff("hn",([EDMain!EDDischargeDate]+[EDMain!EDDischargeTime]),([EDMain!EDPresentationDate]+[EDMain!EDPresentationTime]))

"hn" is a valid operand for Date2Diff, but it is NOT for DateDiff. Either
change it to "n" to get total minutes, or use Date2Diff (once you've included
Date2Diff in your database, it's not a builtin function).

Any ideas as to what might be wrong? I tried to change the date/time fields
to be in the same field, however this did nto go so well!!!

What went wrong? You'ld clearly need to change your data entry forms and
reports (I presume they have two textboxes, for date and time respectively;
this would need to be changed).
--

John W. Vinson [MVP]

.

 

[John W. Vinson]

I have not included the Diff2Dates funciton in my database, so this is
probably why it didnt work! I have had a look at the the posts and the help
funciton about how to include the new funciton, but am a little unsure how to
do this - the whole database was developed by someone else who has since left
my organsieation - there is no one else who can help - the IT department have
not idea about access - I knew more about it than them, and that is saying
something! I somehow changed the settings and lost access to over 1000 data
entries yesterday trying to consolidate the time and date fields to be the
same ones, given I thought that would be a relatively simple exercise I am
now hesitant to press save for anything!

I hope you had a backup...!!! Certainly, make a backup of the entire .mdb
(.accdb) file before making any major changes like this, or even like adding
Diff2Dates.

Could you please let me know how to include the diff2dates funciton - this
seems to be the best option for my issue, as I need to show days, minutes and
hours. I can get to a point where I cut and past the fucntion onto a new
module, but I am not sure what to do from here - I havent saved this yet, if
I save the new module will it affect any of the other items/functions on the
database, or is it just stored there until it is called upon when I enter the
Diff2date in the query section? Other than cut and past, and then save and
name the module - do I have to make any alterations/changes to it? I have
cutt and paste it from a link from the forum.
Thanks for your help

All you need to do is copy and paste the function into a new Module; select
Debug... Compile to be sure there are no compilation errors or missing
libraries (post back if there are such messages). Save the Module but use some
name OTHER than Diff2Dates: modules and procedures can't have duplicate names
(use basDates perhaps). Doing so will not break anything else in the database.

Is this a multiuser database, or just your own?

If it is multiuser, then it should - must! - be Split. Is it? (If you don't
know... it isn't!)

 

[youkr]

John,

So much to learn! My organisation backs up the system every 24hours so I was
saved..

There are only 4 people who use the database, all using the same log in, but
only 2 at anyone time due to shifts - do you still need to split the
database? Is there anyway that you can tell if the database has already been
split? The guy who set it up seems quite IT literate, so I would have thought
he would have done it if it is essential. I have imported data into excel for
anaylsis (prob not the best way to do it - but the only way I knew how),
would splitting the DB now that it has been in use for over a year with these
links established affect any of these?

I really appreciate your time and wisdom

 

[youkr]

An update...

I have successfully added the Diff2Dates and it works perfectly - I am very
impressed by this!!

I tried splitting the database too (only on a copy of the original DB incase
I stuffed up), however it wouldn't complete it as it said that the DB was in
use. The original DB was not in use at the time and I was using a renamed
Copy. I had opened it to split it only - do you have any idea what I could
have done wrong?

 

[John W. Vinson]

John,

So much to learn! My organisation backs up the system every 24hours so I was
saved..

There are only 4 people who use the database, all using the same log in, but
only 2 at anyone time due to shifts - do you still need to split the
database? Is there anyway that you can tell if the database has already been
split? The guy who set it up seems quite IT literate, so I would have thought
he would have done it if it is essential. I have imported data into excel for
anaylsis (prob not the best way to do it - but the only way I knew how),
would splitting the DB now that it has been in use for over a year with these
links established affect any of these?

Split it. Two users is one too many to be safe. You can get away with it
*most* of the time... but people get away with texting while driving, too!

Importing data into Excel would not affect, nor be affected by, splitting.

You can tell if the database is split by looking at the Tables window in the
database window. If the table name has a black arrow icon next to it, it's
linked; or if you hover the mouse over a table name it will show the Connect
string ("C:\SomePath\morepath\xyz.mdb" for instance) if it's split, just the
tablename if it's not.

 

[John W. Vinson]

An update...

I have successfully added the Diff2Dates and it works perfectly - I am very
impressed by this!!

Congratulations! You now have your feet wet.

I tried splitting the database too (only on a copy of the original DB incase
I stuffed up), however it wouldn't complete it as it said that the DB was in
use. The original DB was not in use at the time and I was using a renamed
Copy. I had opened it to split it only - do you have any idea what I could
have done wrong?

Just what did you do? You should be sure that the database compiles without
error (Ctrl-G; Debug... Compile <databasename>), and compact and repair it
before trying to split. Of course if it's already split... don't!

 

[De Jager]

Thanks John,

I have had a go with the date diff function (and the date2diff functions
mentioned on the forum), however end up with #Error. THis is the query I
have
written:

LOS1:
(DateDiff("hn",([EDMain!EDDischargeDate]+[EDMain!EDDischargeTime]),([EDMain!EDPresentationDate]+[EDMain!EDPresentationTime]))

"hn" is a valid operand for Date2Diff, but it is NOT for DateDiff. Either
change it to "n" to get total minutes, or use Date2Diff (once you've
included
Date2Diff in your database, it's not a builtin function).

Any ideas as to what might be wrong? I tried to change the date/time
fields
to be in the same field, however this did nto go so well!!!

What went wrong? You'ld clearly need to change your data entry forms and
reports (I presume they have two textboxes, for date and time
respectively;
this would need to be changed).