Help desperately needed with a formula!

[torie_h]

Hi Guys,

I'm in the process of creating a form but tearing my hair out a bit with this particular part. It's a form to be used for appointments and what I want is for it to alert you when a 4 month questionnaire letter needs to be sent. Theres a box for the day that the appointment was attended and the current date is on the bottom with a =Date() forumula... is there any way to link the two together in an "IF" statement to count 126 days (or 4 months or 16 weeks!!) and say either "letter due", or "not yet due" Because it's driving me up the wall!

If that can't be done like that - is there anything that I can do for it to say "letter due on 23/07/11" or whatever?

Please don't tell me I have to mess around with code... it doesn't like me.

Thanks a lot!

Torie

 

[Trevor G]

Hi Guys,

I'm in the process of creating a form but tearing my hair out a bit with this particular part. It's a form to be used for appointments and what I want is for it to alert you when a 4 month questionnaire letter needs to be sent. Theres a box for the day that the appointment was attended and the current date is on the bottom with a =Date() forumula... is there any way to link the two together in an "IF" statement to count 126 days (or 4 months or 16 weeks!!) and say either "letter due", or "not yet due" Because it's driving me up the wall!

If that can't be done like that - is there anything that I can do for it to say "letter due on 23/07/11" or whatever?

Please don't tell me I have to mess around with code... it doesn't like me.

Thanks a lot!

Torie

Hi Torie,

Can you upload a sample of the database and mention which version of access you are using. It will be easier to look at the form and create a solution. Take any sensitive data out.

 

[JamesMcS]

I did something similar years ago - I had a form that popped up when the database was opened to remind the user of licences that were due to expire. All it was was a form in continuous view, with a filter to say 'show only records where the expiry date is within 90 days' i.e. [expirydate]=now()+90. You could use somthing similar with a filter of [appointmentdate]=now()+126 I expect.

The form also had a button on each record to open up the licencee's details, you could make it pop up a form to show address details, or to mail merge to a word document or whatever.

 

[torie_h]

I'm working on Access 2003 at the moment - here's as far as I've gotten with the blinking thing.

The trouble with the filter is that I wouldn't be the person using it and it would be too much to explain.. unless we could permenantly filter it in order of letters that need to go out? But still be able to view other records as well???? I'm probably asking too much there really aren't I? And it would probably still need to flag up whether a letter is due or not...

Anyway, have a look and let me know what can be done, if anything!

Thank you!

 

[JamesMcS]

You could code it in the form's on open event:

Me.filter="[appointmentdate]=now()+126"
Me.filteron=True

Me.orderby=(Whatever order you like, e.g. appointmentdate DESC"
Me.orderbyon=True

I think what I'd do is have the cover letter and questionnaire as a report. Then, you could just base it on a query and have the criteria against appointment date, and have the user just click a button to open the report.

 

[torie_h]

Code is a dirty word.

I did think about putting the letter as a report but it didn't like it the last time I did that... it struggled to open because it was so big! And because I work for the NHS, most of our computers are the age of the average grain of sand.

 

[JamesMcS]

Nice analogy! I used to work for the NHS, my PC had a crank on it to start it up in the mornings..... Have a crack at mail merging then - I've not used it myself through code (hello swear jar) but I'm sure it's doable.

 

[torie_h]

Yeah, I mean, I've got a mail merge set up at the moment for it so that bit is fine... and I promised all sorts of things expecting getting the computer to count the days to be easy... and as soon as I put pen to paper to try and work it out it just wouldn't happen!!

The closest that I got with a formula is =IF(apptdate>DATE+124,"Letter Due","Not Yet Due") But that doesn't even begin to come close... The fact is that there aren't enough opportunities in a formula to add it all in.... and I HATE code.

As I may have mentioned once or twice before... It just never seems to believe me. It looks at what I type and thinks about it for a moment and then computer says no.

 

[torie_h]

Or even -124...

 

[Trevor G]

As you don't seem to like James idea of using code, you can create a query to list all the records that meets your criteria with the date field, then run the query when you need to check it, like first thing in the morning, then run the appoint report against it, and at the same time get the database to print the appointments and add a date to say it was printed then add that to your criteria.

I am working at home tomorrow so I will look at this and upload a working version, you might need to tweek a few things though to get it working for you.

 

[JamesMcS]

Embrace the VBA! It'll make your life easier in the long run!

 

[torie_h]

Thank you, you lovely chap!!

I don't know how to embrace anything!! All of my access knowledge (the whole thimble-full) has been self taught so I wouldn't know where to start. I tend to find it easier to give up!!

 

[Trevor G]

I have looked at your database and have created an alternative database, it would need a little work on.

I have created 2 tables only, 1 for Patients and 1 for GP and the appointments, I have related them through a PatientID field in the Patient table it is the Primary key and in the GP Field it is a number field so the relationship becomes 1 to Many, in your database there where no 1 to many relationships.

My thoughts are that the Patient will always be the same Patient but they could have many appointments, possibly referred through Different GPs even in the same practice.

I have created a query which will give you the data you need to see the letters that are due to be sent based on a date diff formula with some criteria to show every record above 128 days and that hasn't had a questionnare.

The database has 2 buttons once you open it, to open the query and to go to the patients form. The patients form has the patients details (I haven't adjusted the Tab Order) and subform for the GP.

I've created 3 records to check this through.

In the Postcode fields you had a mask, not the best idea especially if you have some one who comes to you with a shortened postcode so you can't add it, but because the post code should be in capital letters I have added in the format the > symbol this will always make sure the entry goes into Capitals.

There is no VBA code except behind the buttons to open the query and form, but that has been done by the wizard.

Table Names tblPatient and tblGP
Query Name qryLettersDue
Form Names frmStartFrom, frmPatient and frmGP

I hope this helps you get further into this.

 

[Trevor G]

Have you looked at the sample I uploaded as there has been no response to your thread...........