[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
Solved

# Compute the number of days

Posted on 2011-09-06
Medium Priority
320 Views
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
Question by:Chrisjack001
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 12
• 7
• 6
• +1

LVL 120

Expert Comment

ID: 36490060

use this formula

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

Author Comment

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

LVL 34

Expert Comment

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 120

Expert Comment

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 34

Expert Comment

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 34

Expert Comment

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

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

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 34

Expert Comment

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 34

Expert Comment

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

Yes it will.
0

Author Comment

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

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 34

Expert Comment

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

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 34

Expert Comment

ID: 36490500
Here is the image...
0

Author Comment

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

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

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

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
``````
0

Author Comment

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 120

Expert Comment

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

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

Nick67 earned 2000 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

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

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

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

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

LVL 26

Expert Comment

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

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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
What we learned in Webroot's webinar on multi-vector protection.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
###### Suggested Courses
Course of the Month13 days, 20 hours left to enroll

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

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