troubleshooting Question

# Compute the number of days

Microsoft OfficeDatabasesMicrosoft Access
I am using Access 2007
I have a form called “Scheduling” which is used to schedule patients. I want to create a VBA code that will computer the number of days based on the following conditions or take any suggestions that you think is a better solution to accomplishing this goal

When checking in a patient, the “DateOfAppt” field, “ApptTime” field, “Date Out” field and “ApptTimeOut” field are all required fields to fill out.
Whenever the clock hits “11:59 pm” that should be considered as “1” calendar day. Example if a patient is scheduled to come in on 8-25-11 @ 6 pm and checks out on 8-26-11 @ 9 am that will be considered as 2 days based on that condition because the system will count the day the patient came in as 1 day and the new day that started @ 12 midnight. Based on this condition, I want the form to compute the days in the “Number of Days” field. See below for examples of days computed
“DateOfAppt” = 8/26/11
“ApptTime” = 12:00 am
“Date Out” = 8/26/11
“ApptTimeOut” = 9:00 am
“Number of Days” = 1
---------------------------------------------------------------------------------------------------------------------------------------------
“DateOfAppt” = 8/26/11
“ApptTime” = 11:00 pm
“Date Out” = 8/26/11
“ApptTimeOut” = 11:59 pm
“Number of Days” = 1
--------------------------------------------------------------------------------------------------------------------------------------------
“DateOfAppt” = 8/27/11
“ApptTime” = 8:00 am
“Date Out” = 8/28/11
“ApptTimeOut” = 9:00 am
“Number of Days” = 2
DateIn           TimeIn         DateOut        TimeOut      Number of Days
8/25/2011      6:00 PM      8/26/2011      9:00 AM                      2
8/25/2011      11:00 PM     8/27/2011      3:00 PM                3
8/25/2011      8:00 AM      8/28/2011      9:00 AM                 4
8/25/2011      6:00 AM      8/25/2011      9:00 AM                 1
If this is accomplished can a query be created that can capture patients that stay more than 1 day. Example if a patient John Doe checks in on 8/25/11 @ 8:00am and checks out on 8/28/11 @ 10:00am that will be 4 days. Is there a query that can be run for 8/27/11 that will capture John Doe as a patient for that day because he was still admitted in hospital on that day.

This looks very challenging. I wish I could break it up into smaller portions or award more points but it will be confusing. I just thought the expert solving it will find it much easier to accomplish this portion since he/she will have a better understanding. How can I accomplish this goal. Thanks in advance. Attached is the database with the form

Invoice-7-7-2001.accdb
Join the community to see this answer!
###### Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.