Microsoft Access database formula Any help !

Asked By sierra 10 points N/A Posted on - 10/31/2011

Hi Experts,

I need help with Microsoft Access database formula. I need to know how to automatically calculate the date between the first vaccines to the next vaccine date after 1 day. I really need it on my report from our animal shelter.

I already manage to create database for our domestic animals we have. 

Any help ? 

Thanks in advance!

• Status: Open
• Question Views: 880
• Answer Count: 1
• Vote Up 0 Vote Down

• Answer Accepted: No
• Question Category: Microsoft Others

Answered By bmalecs 0 points N/A #99381

Microsoft Access database formula Any help !

To automatically calculate the date between the first vaccines to the next vaccine date, you can use the DATEADD function. The syntax of the function is shown below:

DateAdd(interval, number, date)

Part	Description
Interval	Required. String expression that is the interval of time you want to add.
Number	Required. Numeric expression that is the number of intervals you want to add. It can be positive (to get dates in the future) or negative (to get dates in the past).
Date	Required. Variant (Date) or literal representing date to which the interval is added.

The Interval argument has these settings:

Setting	Description
yyyy	Year
q	Quarter
m	Month
y	Day Of Year
d	Day
w	Weekday
ww	Week
h	Hour
n	Minute
s	Second

Let's look at the following example:

Suppose you create a table give vaccine and also enter next vaccine period. The table my like this:

Id	AnimalName	FirstVaccineDate	NextInMonth
01	Cow	20/11/2011	4
02	Goat	20/11/2011	6

Now to view the date when the next vaccine is due, you can create a query based upon this table, and use the DateAdd function to provide the data you need:

Add an additional column NextVaccineDate and write the query accordingly:

NextVaccineDate: DateAdd("m",[NextInMonth],[FirstVaccineDate])

Now run the quary and you get the desire result.

The resulting data is shown below:

Id	AnimalName	FirstVaccineDate	NextInMonth	NextVaccineDate
01	Cow	20/11/2011	4	20/3/2012
02	Goat	20/11/2011	6	20/5/2012

Thanks.

About bmalecs

Questions
0

Answers
2

Best Answers
1

• Vote Up 0 Vote Down

• Posted on - 11/27/2011
• Question Category: Microsoft Others