calculating dates 30, 60, & 90 days in the future 1

[mlstewart]

I've got a database that was set up for people that are admitted to a nursing home. These are the fiels:

FirstName
LastName
DateAdmitted

They have a form where they enter the data for those three fields. That part is fine. The people at the nursing home want to add some fields that will show dates that are 30 days, 60 days, and 90 day past the DateAdmitted. This is where I need help. They want to store these results in the table as well. The added fields are

30Days
60Days
90Days

How can I calculate these dates and store them in the table?

 

[PHV]

First, its rarely a good idea to store derived/calculated values in a database as you can retrieve the (accurate) info by a query.
Anyway if you insist on denormalzing your schema:
UPDATE yourTable SET [30Days] = DateAdmitted + 30;
You may also consider the DateAdd function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mlstewart]

Thanks PHV, I guess I was trying to make it harder than it really was. I took your advice and did the calculations in a query. Thanks!!

 

[mlstewart]

I've got another issue with the dates. How can I set it up so that it will continue to calculate the dates from today up to one year for now.

--------------
Ex:
DateAdmitted = today's date which is 10/20/2004

For the next year (from 10/20/2004 - 10/20/2005), I want it to return the dates in increments of 90 days.

I don't want to have to go in and manually change the dates 10/20/2004 & 10/20/2005.

Basically, I am trying to get 90 days from today, then 90 days from that date, and then 90 days from that date, etc until one year from now and then view these in a report. I can set up the report if I can get the dates.

I will have to repeat this for 5 days, 14 days, 30 days, & 60 days but I can do all of that if someone can help me figure out how to do just the one for the 90 days.

Thanks!!

 

[MichaelRed]

Conceptually, you just itterate through the period of interest with the invrement. Since virtually all time based processes cease at some future point, ut is -as previously noted- generally done via a calculated value, typicaLy (though not necessarily) via a query. Also, in the normal case, display (and manipulation) of more than a limited number of the repetitive dates in a form becomes 'unweildly' (sp?), so this may provide the clue as to the 'end point' of the calculation(s).

Finally, you mention a number of different segments (5, 14,30, 60, and 90 days. In general, the calculations for hte periord of 30, 60 and 90 days would be done for months, while 14 days would be two weeks. In any case / instance the arbitrary counting of days will result in dats which fall on week-ends and /or holidays, so if these are critical, you will need to adjust to compesate.





MichaelRed

 

[mlstewart]

It doesn't matter if the dates fall on the weekends. The reason they didn't want to use months is because some months have 31 days instead of just 30. This is the way they wanted it set up so that is why I am doing it this way.

This is the deal:
The user will be entering a resident's name and the date they were admitted using a form with text boxes. I've got a query that uses the date admitted to calculate the dates for 5 days, 14 days, 30 days, 60 days, and 90 days from that date of admission. Each resident has an assessment 5 days from admission, then another one 14 days from admission, then another one 30 days from admission, then another one 60 days from admission, then another one 90 days from admission, and then it goes to a quarterly assessment, and then finally an annual assessment. Then, from there on out it will always be an annual assessment.

Now, after typing all of that, I realize that what I was asking in the earlier post isn't what I am needing after all. I apologize.

The thing that I'm not sure about is once it goes to quarterly and then eventually to annully, how can I set it up so that 5 years down the road it contiues to give them the annual date for the assessment?

The user wants to be able to pull up a report with this info on it. For example, they may want view a report that lists everyone that has an assessment due in the next 30 days (and yes I mean from today's date up through the next 30 days).

I really appreciate the help!!

 

[GingerR]

ok here's my cheesy way of doing it.
I made a table called "REVIEWS"
Fields = "QTY" (double, 2 dec places) and "UNIT" (text)

i put in data like this:

Qty Unit
5 d
14 d
30 d
60 d
90 d
6 m
9 m
1 yyyy
2 yyyy
3 yyyy
4 yyyy
5 yyyy
6 yyyy
7 yyyy
8 yyyy
9 yyyy
10 yyyy

Then I made a query, with two tables in it: the PatientInfo table (where the DateAdmitted field comes from) and this new "Reviews" table. Don't make any join between them.

I brought down NAME and DateAdmitted into the Query Grid. Then also a calculated field:

Review Date: DateAdd([unit],[qty],[dateadmitted])

that will add the quanity of the units (i.e. 2 years) to your DateAdmitted field.

Look up DateAdd function in HELP (i used Access 97 help since Access 2002 help sucks). Use the units they use (i.e "d", "m", "yyyy").

Hope this helps!!

 

[MichaelRed]

Other than the use of a Double data type where an integer would suffice (actually be more appropiate), GingerR's soloution is a reasonable deriviation. It does "Assume" the 'quarterly' period(s) are from hte admission date noted, and returns a set of values, so they need to be 'parsed' fo allocate to hte correct control (field) on the form as well as limiting the annual value sets to the ten, but this could easily be ammended by rvrn a novice.



MichaelRed

 

[mlstewart]

Thanks GingerR! Even if you think it is cheesy, it was exactly what I needed!!

ML

 

[GingerR]

i made it double first cause i had .5 year and .75 year instead of 6 months and 9 months, but it wouldn't work right so i changed it to months and didn't think to change the double to integer.