Microsoft Access database formula Any help !

Asked By 10 points N/A Posted on -

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!

Answered By 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


Login/Register to Answer

Related Questions