Solved

Compute the number of days

Posted on 2011-09-06
28
310 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:Chrisjack001
  • 12
  • 7
  • 6
  • +1
28 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36490060

use this formula

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

Author Comment

by:Chrisjack001
ID: 36490078
Can you please tell me where to insert it in the form.
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 36490116
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
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36490128
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
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 36490205
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
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 36490276
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
0
 

Author Comment

by:Chrisjack001
ID: 36490298
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.
0
 

Author Comment

by:Chrisjack001
ID: 36490301
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
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 36490330
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
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 36490340
re:> will the query include the patients name.

Yes it will.
0
 

Author Comment

by:Chrisjack001
ID: 36490348
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
0
 

Author Comment

by:Chrisjack001
ID: 36490366
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
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 36490484
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
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 36490500
Here is the image...
AddButton.png
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Chrisjack001
ID: 36490595
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
0
 

Author Comment

by:Chrisjack001
ID: 36490614
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
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36490817
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
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36490848
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

0
 

Author Comment

by:Chrisjack001
ID: 36490908
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
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36490951
<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()
0
 

Author Comment

by:Chrisjack001
ID: 36491051
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"
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 36491408
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
0
 

Author Comment

by:Chrisjack001
ID: 36491625
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
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36491660
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
0
 

Author Comment

by:Chrisjack001
ID: 36491670
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
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36491707
Depending on how and where you want the cutoff, you need >= or <= and not just equals
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36491720
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
0
 

Author Closing Comment

by:Chrisjack001
ID: 36491770
Nick67 Thanks for your patience. It works great. Thanks you very much
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Read about achieving the basic levels of HRIS security in the workplace.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now