Calculating Days on a form

Dim DateTimeIn As Date
Dim DateTimeOut As Date
Dim NumberOFDays As Integer

DateTimeIn = Me!DateOfAppt + Me!ApptTime
DateTimeOut = Me!Text77 + Me!ApptTimeOut
MsgBox DateDiff("d", DateTimeIn, DateTimeOut) + 1

You can store the value in

DateDiff("d", DateTimeIn, DateTimeOut) + 1

wherever you want.

I am trying to understand your logic. I wonder if you could just give few more examples (to save time you don't have to explain). Just give border line example date/time giving and number of days expected to be reported.

Thanks

DateIn      TimeIn      DateOut      TimeOut      Days
8/25/2011      6:00 PM      8/26/2011      9:00 AM      2 or 3
give more examples…                        

<No Points wanted>

Just a note here and I'll let you continue with eghtebas

If it were me, I would store the date and time in the same table field.
This will avoid issues of the date and time having to be added together every time a date/time calculation is needed.

ex:
ApptDataTimeStart
ApptDateTimeEnd

This would be populated with a value like: 11/20/2011 4:30 PM

Again, I'll let you continue on with eghtebas to avoid confusion.

JeffCoachman

DateIn           TimeIn         DateOut        TimeOut      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

I test all for examples with the solution in my first post (ID: 36432384) and it produced correct answers.

If you try it, you will find it works.

Thanks,

Mike

On your form, you may want Text77 to DateOut  rename to which changes

DateTimeOut = Me!Text77 + Me!ApptTimeOut

to

DateTimeOut = Me!DateOut + Me!ApptTimeOut

Hi Jeff,

re:> 11/20/2011 4:30 PM format compare to having them in separate fields...

The advantage of having appointment time in its own field is the ease of changing the appointment date but keeping the time the same or changing the time but keeping the date the same using calender control or time picker.

thanks,

Mike

Hi eghtebas, where in the form do I put this code

Dim DateTimeIn As Date
Dim DateTimeOut As Date
Dim NumberOFDays As Integer

DateTimeIn = Me!DateOfAppt + Me!ApptTime
DateTimeOut = Me!DateOut + Me!ApptTimeOut
MsgBox DateDiff("d", DateTimeIn, DateTimeOut) + 1

and where on the form do I put this other code. I know you said anywhere but can you be a bit specific

DateDiff("d", DateTimeIn, DateTimeOut) + 1

Since you say it has worked for you based on my examples can you tell me what properties in the form to insert these codes. I am still kind of new to databases.

Thanks

Mike,

<The advantage of having appointment time in its own field is the ease of changing the appointment date but keeping the time the same or changing the time but keeping the date the same using calender control or time picker.>

You are of course, correct...
...Point well taken...

;-)

Jeff

Hi eghtebas, where in the form do I put this code

Dim DateTimeIn As Date
Dim DateTimeOut As Date
Dim NumberOFDays As Integer

DateTimeIn = Me!DateOfAppt + Me!ApptTime
DateTimeOut = Me!DateOut + Me!ApptTimeOut
MsgBox DateDiff("d", DateTimeIn, DateTimeOut) + 1

and where on the form do I put this other code. I know you said anywhere but can you be a bit specific

DateDiff("d", DateTimeIn, DateTimeOut) + 1

Since you say it has worked for you based on my examples can you tell me what properties in the form to insert these codes. I am still kind of new to databases.

Thanks

Sorry for the delay in response. I put on the click event of the form where DateOfAppt etc are. Then I changed the dates per your sample and clicked on the form see whether it give me right answer or not.

brb

I imagine in your case is will be in a function called:

Function fnFindDays() As Integer

Dim DateTimeIn As Date
Dim DateTimeOut As Date
Dim NumberOFDays As Integer

DateTimeIn = Me!DateOfAppt + Me!ApptTime
DateTimeOut = Me!DateOut + Me!ApptTimeOut

' remove the MsgBox after some tests
MsgBox DateDiff("d", DateTimeIn, DateTimeOut) + 1

Me!txtDays = DateDiff("d", DateTimeIn, DateTimeOut) + 1
' txtDays is a new text box where number of days are displayed.
' if you already have a box for it, then use that one.
End Function

Then in the OnCurrent event of the form have:

Dim iDays as Integer
iDays = fnFindDays()

Also, in the after update events of DateOfAppt, ApptTime, DateOut, ApptTimeOut right in the property sheet enter

= fnFindDays()

This way, when you navigate from one record to another, it will fire and update number of days in txtDays.

Mike

also

= fnFindDays()

will update number of days when you change an applointment date or time

Also, the placement of the following code will be anywhere in your form's code window:

Function fnFindDays() As Integer

Dim DateTimeIn As Date
Dim DateTimeOut As Date
Dim NumberOFDays As Integer

DateTimeIn = Me!DateOfAppt + Me!ApptTime
DateTimeOut = Me!DateOut + Me!ApptTimeOut

' remove the MsgBox after some tests
MsgBox DateDiff("d", DateTimeIn, DateTimeOut) + 1

Me!txtDays = DateDiff("d", DateTimeIn, DateTimeOut) + 1
' txtDays is a new text box where number of days are displayed.
' if you already have a box for it, then use that one.
End Function

Just copy and paste it there.

Hi eghtebas, I have followed your instructions and I'm getting errors. I am attaching a copy of the database so you can test my form and see were I went wrong. Can you please fix it and send the database back to me. I'm sure I am missing a step or two.

Thanks
Invoice2003.mdb

Hi Chrisjack001,

I was out for a day or two. I am sorry to see you are having difficulty in putting the code to work for you.

If this is one time access project for you, I understand the hesitation for not getting involved with the project. But, if you will do this as a professon, it is a good idea to roll your sleeves and take the charge of the project.

All you need to do is to copy and paste the code I gave to you earlier and save and run it.

If you say there is an error, you need to be specific by letting us know where the error happens and what the error says.

You can have a break point and step though the code to find where the error is.

If you are not sure what break point and stepping through means, I will be glad to explain them further.

Meanwhile, this evening, I will download and apply the code to your file and give it back to you. But, you will gain a lot by trying to do it yourself.

Regards,

Mike

Chris,

In your original post you indicate:

"Based on this condition, I want the schedule record to be duplicated with an extra day based on the “Date Out” and “ApptTimeOut” field. The duplicate record will have all the data in the above fields on the form except the following will be different."

None of what eghtebas has provide will duplicate records.  All that code will do is compute the number of days, base on your description.

Lets back up a step.  Why do you want to duplicate the original record and then make the date/time changes you have indicated above.  What is the purpose of having these additional records?  Personally I cannot figure out why you would need these additional records in your table.  You can easily compute the  number of days using the DateDiff function (as eghtebas has provide).  Unless you can provide a reasonable answer to this simple question, I would strongly recommend against adding these additional records.

I'm with @fyed on this...
Sometimes just providing code that does what the OP asks isn't the answer.
Sometimes finding out WHY the OP wants a particular snippet is important.

What happens if the person checks in at 26-Aug-11 9:00:00 AM and out at 31-Aug-11 11:59:59 PM...
and then leaves early?
Will you want to delete records?

That isn't a happy thought.

The nuts and bolts of calculating partial days are easy enough
So are the nuts and bolts of duplicating records.
But why are you doing this?

I am thinking there must be a more elegant way to get to your desired end result

Hi Fyed,
           This form is been designed for a department that schedules patients and does the billing. Based upon there requirement, when they schedule a patient and that patient is going to be in the hospital for more than a day, they dont want to go in the system everyday to create additional schedules. They want the system to create the extra records for the extra days. My boss agreed with the idea and requires it to be done that way.

Hi eghtebas,
                   Based on feedbacks from other experts, the code you gave me does not duplicate the extra records as required from my initial question. Can you please confirm that. In regards to rolling my sleeves to do it myself , I will love to do that whenever that opportunity is come there but like I told you in an earlier message, I am fairly new to database and I have a lot to learn. I will guarantee you that as soon as you get that sample database back to me working, I dont just copy it and move on. I will go through it to learn and understand how you achieved that result so next time I will know what to do. I hope you can understand where Im coming from. Once again thanks for all your help

re> Based on feedbacks from other experts...

Originally I read your post couple of times and record duplication did not make any sense at all. I understand you boss want to be done that way but at the end I am afraid you will get the blame. You need to take stand and give a solution to your boss which he needs not he wants.

My suggestion to you is to close this question and start a new one with focus on why record duplication asking a proper way to do the task via some query or routine instead of duplication record. Your office will be better of and you will be appreciated for taking such a positive stand.  

fyed & Nick67 may agree with my suggestion or offer a better suggestion. I have down loaded to apply the code for counting days portion. I will be interested to learn what the other two exports think.

Thanks,

Mike

<they dont want to go in the system everyday to create additional schedules. They want the system to create the extra records for the extra days>
This is where I think all of us have a problem conceptualizing what your application is meant to do.
Your form and tables have bound columns for date and time in and date and time out.
These are not going to make any sense if you are creating 'additional records'
Moreover, how will you go about handling removal of these extra records if the checkout time and date change?
How will you handle ensuring that someone doesn't delete or alter some of the records in the middle of the time span?
These are important stumbling blocks.

I am interested in WHY these 'additional records' are required.
Where (in what table(s)) will they be created?
How will they jive with the multi-day data originally entered into table Scheduling?
Given that form Scheduling displays only a single record, how and where will they display?

I do not grasp the operational flow of what you are requesting.
If you enter an Date Out that is different than the DateOfAppt, then magically the form is to change the time to 11:59:59 PM and create new records from 12:00:00 AM to whatever the Time Out is?  And if there is more than two calendar days between in and out, it should create records from 12:00:00 AM to 11:59:59 PM for each of those days?
That doesn't seem sensible on the face of it.
One patient has one appointment.
If invoicing requires billing-by-day, the place to embody that is when the invoice is calculated.
If viewing a daily workload requires seeing who/how many have appointments in a given time span, that is a place to embody a multi-day span.
Both of those would be calculated when needed, not stored as additional records

More explanation of the business logic you are trying to embody is in order at this point

Chris,

It might be helpful if you could give us a brief description of your data structure.  Can you add the pertinent tables to a new relational diagram and then take a screen shot of that diagram, then post that here?

You might be able to convince me that you need separate records (one for each patient visit day) in some secondary table(s) which is related to your Scheduling table by ScheduleID (which is unique for each patient stay), in order to keep track of actions or personnel assignments that should occur each day during a patients stay.  But I would not do this in your Scheduling table.  In that table, I would only have one record for each patient visit, with a start date/time and an end date/time.  

In my experience, the end date of hospital patient visits are normally open ended, so assigning an End date/time would not make any sense until they are discharged.

To All,

Sorry for the typos etc. in my last post.

I think, having a discriminator field, like DaysNote or DayType, using which the operator can a pick a selection for each record from a drop box with options like:

Exact Days
Extra Days
Report As Duplicate
.
.
.

filled with description like the titles or those that make business sense. Then the user makes appropriate selections for each record which in turn it is used filter future queries to retrieve the data needed. Field [DaysNote] also could be updated automatically via some routine as well.

This idea could be refined after there is a good explanation of why there is need for the duplicate record so we can put together a method that produces the same output without corrupting the data integrity.

Mike    

Also,
Going back to the point @boag2000 made in ID:36432468, you are going to suffer IMMENSE grief with how you are trying to store your dates and times.
You must understand how Access stores dates and times in a fundamental way.
They are stored as double precision numbers.
They ALWAYS contain a date AND a time portion.
Formatting may change what you SEE, but it has no influence on what is STORED.

30-Dec-1899 is day Zero
ALL Date/Time fields where no date is specified will be given day 0 as a default
12:00:00 AM is time Zero
ALL Date/Time fields where no time is specified will be given time 0 as a default

On your second record what you actually have stored is
DateofAppt: 9-Jun-2011 12:00:00 AM
Date Out: NULL
ApptTime: 30-Dec-1899 8:30:00 AM
ApptTimeOut: 30-Dec-1899 9:45:00 AM

If you are going to do calculations with these fields, that is going to be VERY tricky.

:)
Which is another reason we'd like you to explain the business logic you are trying to capture.

There's nothing wrong with trying to display Date and Time in separate controls
Unbound controls, date pickers, time pickers, spin buttons and hidden controls are your friends
There can be BIG problems with trying to store dates and times  in separate fields

And there really isn't any point in me altering up your sample until I understand what you are trying to do

I changed the code to:

Function fnFindDays() As Integer

Dim DateTimeIn As Date
Dim DateTimeOut As Date
Dim NumberOFDays As Integer

'if no date or time supplied, will not calculate #of days
If Nz(Me!DateOfAppt, "") <> "" And _
   Nz(Me!ApptTime, "") <> "" And _
   Nz(Me!DateOut, "") <> "" And _
   Nz(Me!ApptTimeOut, "") <> "" Then
        DateTimeIn = Me!DateOfAppt + Me!ApptTime
        DateTimeOut = Me!DateOut + Me!ApptTimeOut
   
        Me!txtDays = DateDiff("d", DateTimeIn, DateTimeOut) + 1
Else
    'if you like a message pop up, then remove comment mark '
    'MsgBox "No of days are not calculated because In/Out Date/Time not specified."
    Me!txtDays = ""
End If

End Function

I was getting some compile error, so I had to comment few things under save command and elsewhere. I could give exact list if you ask for it. I am usinf Access 2010 but will save it as 2003 and upload it shortly.

Mike

Here is the database.
Invoice2003Revised.mdb

You had:

=========================
Private Sub txtDateOut_Click()   '<-- there was no End Sub. Also you cannot have
                                                ' a function call inside Sub/End Sub
Function fnFindDays() As Integer

Dim DateTimeIn As Date
Dim DateTimeOut As Date
Dim NumberOFDays As Integer

DateTimeIn = Me!DateOfAppt + Me!ApptTime
DateTimeOut = Me!DateOut + Me!ApptTimeOut

' remove the MsgBox after some tests
MsgBox DateDiff("d", DateTimeIn, DateTimeOut) + 1

Me!txtDays = DateDiff("d", DateTimeIn, DateTimeOut) + 1
' txtDays is a new text box where number of days are displayed.
' if you already have a box for it, then use that one.

End Function
==========
If you want to call this routine from another event, either enter:
=fnFindDays()       'which you have done properly on the property sheet

of, if you want to call from an even in the code window do whatever you have in OnCurrent event:

Dim iDays As Integer
iDays = fnFindDays()