Access 2007 - calculate future date

[ljoekelsoey4]

Hello.

I'm relatively new to access 2007, having been studying / working at it for around a month now.

I'm creating essentially a log medical database, that is a record of all patients and all their appointments, and not much else. I'm trying to create a macro (or indeed to code) that allows a future appointment date to be calculated based on what the person enters as the required elapsed time.

For example, a patient comes in today (15th March 2017) and the outcome of the clinic visit, is that the doctor wants to see them back in 4 months. What I want is to be able to then calculate the date out of this so that it can be used to report patients who are due in for the next week.

Suggestions I had come up with were 2 boxes; one for a number (4) and another for the qualifier (months), and the prospective date would then be shown in a box on the side. This would show on the form where the doctor is putting in the rest of the appointment information.

I can't quite figure out how to get this working. Would appreciate any help

Thanks a million,

Alex

 

[CJ_London]

use the dateadd function in an unbound textbox control

=dateadd(me.qualifier,me.period,[appointmentDate])

 

[ljoekelsoey4]

Thanks for the quick reply, but I'm not really sure how to make that work. I'm assuming I'd name the boxes as 'period' and 'qualifier' as unbound text boxes - would combo boxes work? - and then in the unbound text box where I want the date to be shown, type in that dateadd function? Would i need to set code to count afterupdate?

Thanks

 

[CJ_London]

would combo boxes work?

yes - probably with a 2 column value list - something like

"m";"Months";"yyyy";"Years".... etc

bound to column 1 and column 0 hidden (not binding starts from 1 and columns from 0 - so in the above, bound column 1 is "m", column(0) is also "m"

assuming names match the names of your controls, just copy and paste the code into the controlsource of the unbound control

 

[ljoekelsoey4]

I'm really not sure where I'm going wrong, but it simply isn't working.:banghead:

 

[ljoekelsoey4]

Got it working. Thanks a million.

Next, I want to be able to save that calculated date into the 'patients' table, as a 'follow up date'. Having trouble working out how to bind an unbound auto-populating text box to a different table.

Currently not an issue, but when the same patient comes in again, the new 'next appointment date' would need to overwrite the existing one.

Thanks a million,
Alex.

 

[CJ_London]

Having trouble working out how to bind an unbound auto-populating text box to a different table.

you would do it differently. The other table would be displayed in a subform on your main table and instead of having the calculation in the controlsource you would use vba code to populate it. The code would be triggered perhaps in the period control after update event.

the code would be something like

me.subformname.form.nextappointment=dateadd(me.qualifier,me.period,[appointmentDate])

Currently not an issue, but when the same patient comes in again, the new 'next appointment date' would need to overwrite the existing one.

that is not the normal way to do things - if you have a separate table that table should have a field or fields to identify the record as a relation to the treatment, practitioner etc. If I go to the dentist I might make 3 appointments - finish current treatment, next checkup and a clean. If you just want a single next appointment, it would be a field in the client record.

Think you need to read up on normalisation and how to design a database and map out properly how your business works before starting to create your db. At the moment it seems you are making it up as you go along which is the worst way by far. You'll go down many blind alleys and waste a lot of time

 

[ljoekelsoey4]

you would do it differently. The other table would be displayed in a subform on your main table and instead of having the calculation in the controlsource you would use vba code to populate it. The code would be triggered perhaps in the period control after update event.

the code would be something like

me.subformname.form.nextappointment=dateadd(me.qualifier,me.period,[appointmentDate])

this seems more logical for what I'm doing, I'll give it a try, thanks.

that is not the normal way to do things - if you have a separate table that table should have a field or fields to identify the record as a relation to the treatment, practitioner etc. If I go to the dentist I might make 3 appointments - finish current treatment, next checkup and a clean. If you just want a single next appointment, it would be a field in the client record.

I apologise for not making myself particularly clear. The patient table and the appointment tables are entirely seperate, linked only by the patient ID. On top of that I'm running a query of active patients, that is, patients who haven't died, who are needing follow up, etc. In that query I'm running a 'max of' appointment to highlight patients most recent appointment, and in that query I'd use the 'next appointment' date, so that I can run reports highlighting patients, for example, who are due in within next 2 months, etc.