By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,449 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

Combination of Date and Day in DateAdd

peeaurjee
P: 34
Hello,

I have a query containing Serial, Name, Residence Visa Expiry Date in MS Access 2003. I need to put Residence Visa Registration Date in other field and that would be in reverse I mean 3 years and 1 day back. For Example;

Residence Visa Expiry Date = 20/12/2010
Residence Visa Registration Date = 21/12/2007

The above one is the result I want, means expiry date is 3 years and 1 day before the registration date.

What I tried is;

Registration Date: DateAdd("yyyy",-3,[Residence Visa Exp Date])

which gives me,

Residence Visa Expiry Date = 20/12/2010
Residence Visa Registration Date = 20/12/2007

That I don't want and I tried to add "dd",1 like part of syntaxes but I failed.
Please help me regarding this problem.
Aug 13 '08 #1
Share this Question
Share on Google+
4 Replies


100+
P: 167
Hello,

I have a query containing Serial, Name, Residence Visa Expiry Date in MS Access 2003. I need to put Residence Visa Registration Date in other field and that would be in reverse I mean 3 years and 1 day back. For Example;

Residence Visa Expiry Date = 20/12/2010
Residence Visa Registration Date = 21/12/2007

The above one is the result I want, means expiry date is 3 years and 1 day before the registration date.

What I tried is;

Registration Date: DateAdd("yyyy",-3,[Residence Visa Exp Date])

which gives me,

Residence Visa Expiry Date = 20/12/2010
Residence Visa Registration Date = 20/12/2007

That I don't want and I tried to add "dd",1 like part of syntaxes but I failed.
Please help me regarding this problem.
Your second try should use only one "d" like this (query):
Expand|Select|Wrap|Line Numbers
  1. Registration Date: DateAdd("d";1;DateAdd("yyyy";-3;[Residence Visa Exp Date]))
Aug 13 '08 #2

peeaurjee
P: 34
Your second try should use only one "d" like this (query):
Expand|Select|Wrap|Line Numbers
  1. Registration Date: DateAdd("d";1;DateAdd("yyyy";-3;[Residence Visa Exp Date]))
Thank You Very Much.....It worked.

Thanks once again..
Aug 14 '08 #3

P: 23
But why nesting of both Formula?

DateAdd("yyyy",-3,[Residence Visa Exp Date])+1 will give you the same result.

a.p.r. pillai
** Link Removed **
Aug 15 '08 #4

NeoPa
Expert Mod 15k+
P: 31,186
Some people prefer to code without relying on the numerical equivalences of date values to numbers.

We know that a day is a single unit when stored as an Access date, but is it guaranteed always to be so?
Aug 20 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.