Compute the number of days

Chrisjack001
Chrisjack001 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:

use this formula

datediff("d",[dateIn],[dateout]) + 1

Author

Commented:
Can you please tell me where to insert it in the form.
Mike EghtebasDatabase and Application Developer

Commented:
Chrisjack001,

For a data like you have, is the following acceptable? if we are running the qryCurrentPatients, it will give a result like:

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

qryCurrentPatients runs on 8/26/2011:
DateIn           TimeIn         DateOut        TimeOut      CurrentPatients?
8/25/2011      6:00 PM      8/26/2011      9:00 AM                 Y
8/25/2011      11:00 PM     8/27/2011      3:00 PM                Y
8/25/2011      8:00 AM      8/28/2011      9:00 AM                 Y
8/25/2011      6:00 AM      8/25/2011      9:00 AM                 Y

qryCurrentPatients runs on 8/27/2011:
DateIn           TimeIn         DateOut        TimeOut      CurrentPatients?
8/25/2011      6:00 PM      8/26/2011      9:00 AM                 N
8/25/2011      11:00 PM     8/27/2011      3:00 PM                Y
8/25/2011      8:00 AM      8/28/2011      9:00 AM                 Y
8/25/2011      6:00 AM      8/25/2011      9:00 AM                 N

qryCurrentPatients runs on 8/28/2011:
DateIn           TimeIn         DateOut        TimeOut      CurrentPatients?
8/25/2011      6:00 PM      8/26/2011      9:00 AM                 N
8/25/2011      11:00 PM     8/27/2011      3:00 PM                Y
8/25/2011      8:00 AM      8/28/2011      9:00 AM                 Y
8/25/2011      6:00 AM      8/25/2011      9:00 AM                 N

qryCurrentPatients runs on 8/29/2011:
DateIn           TimeIn         DateOut        TimeOut      CurrentPatients?
8/25/2011      6:00 PM      8/26/2011      9:00 AM                 N
8/25/2011      11:00 PM     8/27/2011      3:00 PM                N
8/25/2011      8:00 AM      8/28/2011      9:00 AM                 N
8/25/2011      6:00 AM      8/25/2011      9:00 AM                 N

Mike
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2016

Commented:
you can include that in your query

select DateIn, TimeIn, DateOut,TimeOut, datediff("d",[dateIn],[dateout]) + 1 as [Number of Days]
from tableName


then set the control source of the textbox to Number of days
Mike EghtebasDatabase and Application Developer

Commented:
Cap,

For background see:  http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27276070.html

Chrisjack001,

If my last post in this thread is acceptable, then you need to build a query like
Select PatientName, [dateIn], TimeIn, NumberOfDays As NoOfDays_ToDate from Scheduling Where Nz(NumberOfDays) = 0;

I will add a button to your application with caption: "Current Patients" which will open a query reporting the list of current patients.

brb
Mike EghtebasDatabase and Application Developer

Commented:
I had to change the query to:


SELECT Scheduling.PatientName, Scheduling.DateOfAppt, Scheduling.ApptTime, Scheduling.NumberOfDays AS NoOfDays_ToDate
FROM Scheduling
WHERE (((Nz([NumberOfDays]))=0));

Test the attached database to see how do you like it. Please note this is the same database I uploaded for your use in the last question. You still need to do the necessary adjustment to it as before. Also, the data in it belongs to the older version.

Mike
Invoice2003RevisedSep6.mdb

Author

Commented:
Hi guys. I'm kind of confused right now on what to do. If I could understand eghtebas will the query include the patients name.

Author

Commented:
Hi Capricorn,
What query are you talking about?

you can include that in your query
select DateIn, TimeIn, DateOut,TimeOut, datediff("d",[dateIn],[dateout]) + 1 as [Number of Days]
from tableName
then set the control source of the textbox to Number of days
Mike EghtebasDatabase and Application Developer

Commented:
Basically, the logic I used was to look at number of days. If a patient doesn't have DayOut in the database, his/her number of days in the hospital is not calculated yet (thus still a patient).

Mike
Mike EghtebasDatabase and Application Developer

Commented:
re:> will the query include the patients name.

Yes it will.

Author

Commented:
eghtebas you removed some of my codes which resulted in certain functions not working anymore like when you select a patient name, the DOB is automatically populated but that changed and since another exper from that thread suggested I close that question and open a new one I thought I should just ask the question about calculating the days without duplicating the record X number of times and having a query that could be run to capture data for patients that stay more than 1 day and for a specific date

Author

Commented:
eightbas please try to use my sample database and create what you did in the last thread without deleting some of the existing codes which resulted in some of the other functions not working
Mike EghtebasDatabase and Application Developer

Commented:
re:> please try to use my sample database and create ...

Please follow these steps:

1. Add a button to your form. Name it cmdCurrentPatients as shown on the attached image below.

2. In the design mode select this button and via the property sheet under events access its click events which will look like this:

Private Sub cmdCurrentPatients_Click()

   
End Sub

3. Between Private sub/End sub add:  DoCmd.OpenQuery "qryCurrentPatients"  which will look like this:

Private Sub cmdCurrentPatients_Click()

    DoCmd.OpenQuery "qryCurrentPatients"

End Sub

4. Now when you click on this button, it will attempt to open query named qryCurrentPatients. But you don't have it in your version of the database. So, You need to import query named qryCurrentPatients from the database I just uploaded for you.

Mike
Mike EghtebasDatabase and Application Developer

Commented:
Here is the image...
AddButton.png

Author

Commented:
Eghtebas is it possible for that query you created to prompt the user for dates. Meaning they can query not only for the current date, but for a past date or a future date

Author

Commented:
How come the DOB does not automatically populate on your database sample sent when a patient name is selected? Can you please try it on the 2007 version I sent because thats what I use
Most Valuable Expert 2014

Commented:
Ok,

Look at the sample altered.
The form has a new textbox txtBillingDays
The four time fields have AfterUpdate events and there's a sub to calculate the possibilites
Invoice2003.mdb
Most Valuable Expert 2014

Commented:
Given that you can navigate the form, throw

Call CalcBillingDays

in the Form_Current() event too

Here's the code that fills the textbox
Private Sub CalcBillingDays()
'Ok, while the data storage is ugly, it is what it is
Select Case True
    Case Nz(Me.DateOfAppt, 0) > Nz(Me.DateOut, #12/31/9999#)
        MsgBox "Nonsense! The appointment ends before it starts"
        Me.txtBillingDays = 0
        Exit Sub
    Case (Nz(Me.DateOut, "") = "" Or (Nz(Me.DateOfAppt, 0) = Nz(Me.DateOut, 0))) And (Nz(Me.ApptTime, 0) > Nz(Me.ApptTimeOut, 0))
        MsgBox "Nonsense! The appointment ends before it starts"
        Me.txtBillingDays = 0
        Exit Sub
    Case Nz(Me.DateOfAppt, "") = "" 'blank appointment date--bail
        Me.txtBillingDays = 0
        Exit Sub
    Case Nz(Me.ApptTime, "") = "" 'blank appointment time--bail
        Me.txtBillingDays = 0
        Exit Sub
    Case Nz(Me.ApptTimeOut, "") = "" 'blank appointment end--bail
        Me.txtBillingDays = 0
        Exit Sub
    Case Nz(Me.DateOut, "") = "" Or (Me.DateOfAppt = Me.DateOut)
        'date in = date out, therefore one billing day
        Me.txtBillingDays = 1
        Exit Sub
    Case Else
        'ok the toughie
        'one day for the date in, one day for the day out, and a day for each where the diff is greater than 1
        'fix throws away the time part of the DateSerial
        'CDbl coerces it to a number for math
        'Cdate coerces any possible string literal dates to date values
        Me.txtBillingDays = 2 + (Fix(CDbl(CDate(Me.DateOut)) - Fix(CDbl(CDate(Me.DateOfAppt))))) - 1
End Select


End Sub

Open in new window

Author

Commented:
How about the button query  that can captures the list of patients for a specific date based on the date the user enters when they click on the button.  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 and all other patients
Top Expert 2016

Commented:
<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 and all other patients >

try this

select * from table
where Date() between [DateIn] and [DateOut]

or something like this

select * from table
where  [DateIn] >= Date() and [DateOut] <=Date()

Author

Commented:
should I put this query on a button. and if so in what property. Will the query produce a list that has "PatientName", "DateOfAppt", "ApptTime", "Date Out", "ApptTimeOut" and "Number of Days"
Most Valuable Expert 2014
Commented:
See altered sample.
It has a button for your query
CalcBillingDays has been made into a Public Function so it can be used in queries
Your query is created.
A button to run it is added.
Invoice2003.mdb

Author

Commented:
Hi Nick67,
         I added some more new schedules to the table with different date ranges to test your query. I noticed that when you input a start date in only select records that have there "DateOfAppt" that where originally entered on that day. For example with these 2 records entered

DateIn           TimeIn         DateOut        TimeOut      Number of Days
8/25/2011      6:00 PM      8/26/2011      9:00 AM                 2
8/24/2011      8:00 AM      8/28/2011      9:00 AM                 5

When I queried it for Start date of 8-25-11 it only selected the first record but I want the query to select the records of all patient that are still admitted on 8-25-11 which means the second record should be included. Thanks for your patience
Most Valuable Expert 2014

Commented:
Open the query in Design view.
Cut the >=[Enter a Start Date] from the field it's in (dateofAppt) and paste it into DateOut
Then try it

Author

Commented:
Nick I had a typo
I added some more new schedules to the table with different date ranges to test your query. I noticed that when you input a start date,  it only selects records that have the same "DateOfAppt" that is equal to the date the user entered in the query
Most Valuable Expert 2014

Commented:
Depending on how and where you want the cutoff, you need >= or <= and not just equals
Most Valuable Expert 2014

Commented:
If you aren't entering the parameter through the dialog prompt, but directly in the query you'll need hashes too

>=#16-Jul-2011# and not

>=16-Jul-2011

Author

Commented:
Nick67 Thanks for your patience. It works great. Thanks you very much

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial