• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 330
  • Last Modified:

Compute the number of days

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
Chrisjack001
Asked:
Chrisjack001
  • 12
  • 7
  • 6
  • +1
1 Solution
 
Rey Obrero (Capricorn1)Commented:

use this formula

datediff("d",[dateIn],[dateout]) + 1
0
 
Chrisjack001Author Commented:
Can you please tell me where to insert it in the form.
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Rey Obrero (Capricorn1)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
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
Chrisjack001Author 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.
0
 
Chrisjack001Author 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
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
Mike EghtebasDatabase and Application DeveloperCommented:
re:> will the query include the patients name.

Yes it will.
0
 
Chrisjack001Author 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
0
 
Chrisjack001Author 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
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
Mike EghtebasDatabase and Application DeveloperCommented:
Here is the image...
AddButton.png
0
 
Chrisjack001Author 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
0
 
Chrisjack001Author 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
0
 
Nick67Commented:
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
 
Nick67Commented:
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
 
Chrisjack001Author 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
0
 
Rey Obrero (Capricorn1)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()
0
 
Chrisjack001Author 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"
0
 
Nick67Commented:
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
 
Chrisjack001Author 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
0
 
Nick67Commented:
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
 
Chrisjack001Author 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
0
 
Nick67Commented:
Depending on how and where you want the cutoff, you need >= or <= and not just equals
0
 
Nick67Commented:
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
 
Chrisjack001Author Commented:
Nick67 Thanks for your patience. It works great. Thanks you very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 12
  • 7
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now