Please help with Date Function

[Guest]

Hello all,

I have created a date field (A) to keep track of which books to destroy. I
have it set up to populate the field automatically when we add a new book
into the database. It is set up in the BeforeUpdate of a form, and I am
trying to write a formula to add 10 years from the date the book is added
into the database.

Below is the formula:

me.[A].value = now(year()+10,month(), day())

It does not seem to be working. I am getting an error.

Please help. Thanks.

 

[Guest]

Try using DateAdd to add 10 years to the current date

me.[A] = DateAdd("yyyy",10,Date())

 

[Guest]

Good morning Ofer,

Thanks very much for your help. You are good, and your code works
perfectly. I am just curious which part of the code knows that add 10 years
from today's date (date created)?

Thanks again.

Try using DateAdd to add 10 years to the current date

me.[A] = DateAdd("yyyy",10,Date())


--
Good Luck
BS"D

Hello all,

I have created a date field (A) to keep track of which books to destroy. I
have it set up to populate the field automatically when we add a new book
into the database. It is set up in the BeforeUpdate of a form, and I am
trying to write a formula to add 10 years from the date the book is added
into the database.

Below is the formula:

me.[A].value = now(year()+10,month(), day())

It does not seem to be working. I am getting an error.

Please help. Thanks.

 

[Guest]

You can check Access help file to see the different options of the DateAdd
function.

DateAdd(Interval , number , date)

Interval - "yyyy" for years
number - 10 to add years to the date
Date() - Return the Current date

The help file will specify the different intervals
"m" - month
"d" - days

etc

--
Good Luck
BS"D

Good morning Ofer,

Thanks very much for your help. You are good, and your code works
perfectly. I am just curious which part of the code knows that add 10 years
from today's date (date created)?

Thanks again.

Try using DateAdd to add 10 years to the current date

me.[A] = DateAdd("yyyy",10,Date())


--
Good Luck
BS"D

Hello all,

I have created a date field (A) to keep track of which books to destroy. I
have it set up to populate the field automatically when we add a new book
into the database. It is set up in the BeforeUpdate of a form, and I am
trying to write a formula to add 10 years from the date the book is added
into the database.

Below is the formula:

me.[A].value = now(year()+10,month(), day())

It does not seem to be working. I am getting an error.

Please help. Thanks.

 

[Guest]

Hi Ofer,

Thanks again for your helps. Can you help me one more thing?

I am trying to design a report to show the inventory of books that are added
today. In the Record Source of the report, I put "Date()" in the criteria
field assuming that it will bring up a list of books added today. When I run
the query, it brings up nothing.

Did I use the right one in the criteria field?

Thanks.

You can check Access help file to see the different options of the DateAdd
function.

DateAdd(Interval , number , date)

Interval - "yyyy" for years
number - 10 to add years to the date
Date() - Return the Current date

The help file will specify the different intervals
"m" - month
"d" - days

etc

--
Good Luck
BS"D

Good morning Ofer,

Thanks very much for your help. You are good, and your code works
perfectly. I am just curious which part of the code knows that add 10 years
from today's date (date created)?

Thanks again.

Try using DateAdd to add 10 years to the current date

me.[A] = DateAdd("yyyy",10,Date())


--
Good Luck
BS"D


:

Hello all,

I have created a date field (A) to keep track of which books to destroy. I
have it set up to populate the field automatically when we add a new book
into the database. It is set up in the BeforeUpdate of a form, and I am
trying to write a formula to add 10 years from the date the book is added
into the database.

Below is the formula:

me.[A].value = now(year()+10,month(), day())

It does not seem to be working. I am getting an error.

Please help. Thanks.

 

[John W. Vinson]

Hi Ofer,

Thanks again for your helps. Can you help me one more thing?

I am trying to design a report to show the inventory of books that are added
today. In the Record Source of the report, I put "Date()" in the criteria
field assuming that it will bring up a list of books added today. When I run
the query, it brings up nothing.

Did I use the right one in the criteria field?

That depends on what's in the date field. Date() returns today's date, with an
implied time portion of midnight; Now() returns the current date and time,
accurate to a few microseconds (though you can only display to the nearest
second).

If you're filling the field with Now() use a criterion

= Date() AND < Date() + 1

John W. Vinson [MVP]

 

[Guest]

Hi John,

Thanks for your helps. Your code ">= Date() AND < Date() + 1" works. I
don't understand something. Why do we that many variations? Are these
variations logical?

Can you tell me how Access is translating your code vs. =Date()?

Thanks.

 

[John W. Vinson]

Hi John,

Thanks for your helps. Your code ">= Date() AND < Date() + 1" works. I
don't understand something. Why do we that many variations? Are these
variations logical?

There's usually lots of logical ways to get something done; it depends on just
what you want to get done.

Can you tell me how Access is translating your code vs. =Date()?

Let's say you're filling a date/time field using Now(). If I were to enter a
record right now, the field would contain

#7/12/2007 3:22:25 PM #

which is actually stored as

39275.6405671296

If you use a criterion on =Date() it will match #7/12/2007 00:00:00# -
midnight last night, the start of the day, stored as 39275.000000000. This
match will apply regardless of how the field is formatted.

My criterion

= Date() AND < Date() + 1

will match any value between 39275.0000000000 and 39275.9999999999, that is,
any time during the day.

John W. Vinson [MVP]

 

[Guest]

Good morning John,

Thanks for taking the time to explain me the code. You are genius and very
knowledge. What do you think I can do only 10% of what you know about Access?

Thanks.

 

[John W. Vinson]

Thanks for taking the time to explain me the code. You are genius and very
knowledge. What do you think I can do only 10% of what you know about Access?

<blush>

Well, I've been using it since 1992 or so, in my business, and much more here
on the newsgroups. I've seen gazillions of questions, many over and over, and
if I don't know the answer I'll watch to see if someone else does, or dig
through the documentation and find out. For me there's no better way to learn.

John W. Vinson [MVP]