Date

[zyus]

I have a date field [fstrlsedt] and a number field [prd].

The number field is actually refering to number of months.

How to put an expression to find a new date by adding [fstrlsedt] with the
number of months [prd]

 

[zyus]

What i'm expecting is to get new date as follow

Assume [fstrlsedt] = "01-01-2010" and [prd]=12

my new date will be "01-01-2011"

I have a date field [fstrlsedt] and a number field [prd].

The number field is actually refering to number of months.

How to put an expression to find a new date by adding [fstrlsedt] with the
number of months [prd]

Hmm... the DateAdd function sounds awfully promising!!! Create an unbound
control and use something like

DateAdd("m", me.Controls("fstrlsedt"), me.controls("prd"))

Awfully descriptive control names... I hope you never give someone your
database to work on...

--
Message posted via AccessMonster.com


.

 

[GP George]

That's exactly what Pieter's suggestion should return, so I'm wondering what
happened when you tried it?

What i'm expecting is to get new date as follow

Assume [fstrlsedt] = "01-01-2010" and [prd]=12

my new date will be "01-01-2011"

I have a date field [fstrlsedt] and a number field [prd].

The number field is actually refering to number of months.

How to put an expression to find a new date by adding [fstrlsedt] with
the
number of months [prd]

Hmm... the DateAdd function sounds awfully promising!!! Create an
unbound
control and use something like

DateAdd("m", me.Controls("fstrlsedt"), me.controls("prd"))

Awfully descriptive control names... I hope you never give someone your
database to work on...

--
Message posted via AccessMonster.com


.

 

[zyus]

When i put DateAdd("m", me.Controls("fstrlsedt"), me.controls("prd"))
in my query error of "Undefined function "me.controls" in expression

Then i changed to DateAdd("m",[fstrlsedt],[prd]) but the result is not correct

eg the [fstrlsedt]=30/04/2010 and [prd]=60 and the was new date = 28/04/5258

That's exactly what Pieter's suggestion should return, so I'm wondering what
happened when you tried it?

What i'm expecting is to get new date as follow

Assume [fstrlsedt] = "01-01-2010" and [prd]=12

my new date will be "01-01-2011"

zyus wrote:
I have a date field [fstrlsedt] and a number field [prd].

The number field is actually refering to number of months.

How to put an expression to find a new date by adding [fstrlsedt] with
the
number of months [prd]

Hmm... the DateAdd function sounds awfully promising!!! Create an
unbound
control and use something like

DateAdd("m", me.Controls("fstrlsedt"), me.controls("prd"))

Awfully descriptive control names... I hope you never give someone your
database to work on...

--
Message posted via AccessMonster.com


.

 

[zyus]

Try my best to answer your question,

I've tried the expression in a query.
I intend to identify accounts that going to mature in dec 2010. as
[fstrlsedt] is the first release date and when i add number of month [prd] i
will get each account maturity date.

Please bear with me on the field names as i use access for analysis &
reporting data that i imported from other source. It's not meant for a
"proper" database system. So far agree with you that i'm the only user of my
database

What i'm expecting is to get new date as follow

Assume [fstrlsedt] = "01-01-2010" and [prd]=12

my new date will be "01-01-2011"

I have a date field [fstrlsedt] and a number field [prd].

[quoted text clipped - 10 lines]

Awfully descriptive control names... I hope you never give someone your
database to work on...

As George pointed out... clearly, you haven't tried it... where did you
intend to put this new date? What did you need to do with it?

If you need to be able to search for it, you have to add it to the form's
underlying recordsource (by creating a query based on the original
recordsource and then adding the expression to the query). Then you can
search for/sort by it.
If you just need to *display* it, then you can just create an unbound control
and set the controlsource.
If you have to create another record, that's a different story... I guess I'd
use DBEngine(0)(0).Execute and create a dynamic SQL string to do it...

Oh, and while you're at it, those field names really need some work - unless
you are the only one to ever work on this database. It's just a lot easier
to figure out after a long time away if the field and control names actually
make some kind of sense.

--
Message posted via AccessMonster.com


.

 

[John Spencer]

You have the arguments in the wrong order

DateAdd("m",[prd],[fstrlsedt])

Also, I would be concerned if [fstrlsedt] is a string/text field and not a
datetime field that you may get erroneous dates returned.

If the field is storing the date as a text string then see the Allen Browne's
article on the problem with non-US format dates.
International Dates in Access at:
http://allenbrowne.com/ser-36.html

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

When i put DateAdd("m", me.Controls("fstrlsedt"), me.controls("prd"))
in my query error of "Undefined function "me.controls" in expression

Then i changed to DateAdd("m",[fstrlsedt],[prd]) but the result is not correct

eg the [fstrlsedt]=30/04/2010 and [prd]=60 and the was new date = 28/04/5258

That's exactly what Pieter's suggestion should return, so I'm wondering what
happened when you tried it?

What i'm expecting is to get new date as follow

Assume [fstrlsedt] = "01-01-2010" and [prd]=12

my new date will be "01-01-2011"



:

zyus wrote:
I have a date field [fstrlsedt] and a number field [prd].

The number field is actually refering to number of months.

How to put an expression to find a new date by adding [fstrlsedt] with
the
number of months [prd]
Hmm... the DateAdd function sounds awfully promising!!! Create an
unbound
control and use something like

DateAdd("m", me.Controls("fstrlsedt"), me.controls("prd"))

Awfully descriptive control names... I hope you never give someone your
database to work on...

--
Message posted via AccessMonster.com


.