Please help with Date Function

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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.

Ofer Cohen said:
Try using DateAdd to add 10 years to the current date

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


--
Good Luck
BS"D


AccessHelp said:
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.
Click to expand...
Click to expand...
 
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


AccessHelp said:
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.

Ofer Cohen said:
Try using DateAdd to add 10 years to the current date

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


--
Good Luck
BS"D


AccessHelp said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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.

Ofer Cohen said:
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


AccessHelp said:
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.

Ofer Cohen said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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?
Click to expand...

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
Click to expand...


John W. Vinson [MVP]
 
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.
 
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?
Click to expand...

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()?
Click to expand...

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
Click to expand...

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

John W. Vinson [MVP]
 
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.
 
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?
Click to expand...

<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]
 
Back
Top