calculate expiration dates in Access

By chocolatte2 ·
I'm working with Access 2007. I'm working on a database used to store info about my pets health record. Every time my pet goes to the vet, I record what was done. Some dates 1 service was done, other dates several things were done. My problem is calculating the due dates for some services, like vaccinations. For example, if only a Rabies vaccine was given on 1/1/2007, I get the due date of 1/1/2008 using DateAdd which is correct but I also get a due date of 1/1/2008 for all the other services even though they were done on a different date. How can I get Access to pull the most recent date for a service and calculate the due date for that service only?
The form is laid out with a main form that has 3 subforms:
Main Form = frmPetInfoMain
Subform = sfrmMedicalInfo
Subform = sfrmExpiration
Subform = sfrmMedication

A date and service are entered in sfrmMedicalInfo. I want Access to take the most recent date a service was done, find how many months ahead to make the due date (listed in a separate table), and put the due date in sfrmExpiration.
Any suggestions would be appreciated.
Thanks, Charlotte

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

The update is hitting more than one record

by Tony Hopkinson In reply to calculate expiration date ...

no way to tell from here.


If you had

ServiceName ServiceCompleted ServiceDue
Rabies 1/1/2007 1/1/2008
Diptheria 6/2/2007 6/2/2008

The only way they are all getting updated, is if you forgot Where ServiceName = 'Rabies'

Personally I'd change the design

ServiceID ServiceName ServiceFrequency
1 Rabies 12 (months)
2 Diptheria 6

PetID ServiceID ServiceDone

and then just calculate the expiry date, that way you don't have to go updating loads of stuff, if you go in a month later, or find out now it's more than once a year now.

Also if you make Serviced have a key of the above three fields, you get a real history instead of just one actual date.


Related Discussions

Related Forums