|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 25th, 2010, 02:17 AM
posted to microsoft.public.access
|
|||
|
|||
Date
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] 
|
|
#2
May 25th, 2010, 03:04 AM
posted to microsoft.public.access
|
|||
|
|||
|
Date
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 http://www.accessmonster.com/Uwe/For...ccess/201005/1
|
|
#3
May 25th, 2010, 04:52 AM
posted to microsoft.public.access
|
|||
|
|||
|
Date
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" "PieterLinden via AccessMonster.com" wrote: 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 http://www.accessmonster.com/Uwe/For...ccess/201005/1 .
|
|
#4
May 25th, 2010, 06:39 AM
posted to microsoft.public.access
|
|||
|
|||
|
Date
That's exactly what Pieter's suggestion should return, so I'm wondering what
happened when you tried it? "zyus" wrote in message ... 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" "PieterLinden via AccessMonster.com" wrote: 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 http://www.accessmonster.com/Uwe/For...ccess/201005/1 .
|
|
#5
May 25th, 2010, 07:12 AM
posted to microsoft.public.access
|
|||
|
|||
|
Date
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 "GP George" wrote: That's exactly what Pieter's suggestion should return, so I'm wondering what happened when you tried it? "zyus" wrote in message ... 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" "PieterLinden via AccessMonster.com" wrote: 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 http://www.accessmonster.com/Uwe/For...ccess/201005/1 .
|
|
#6
May 25th, 2010, 08:38 AM
posted to microsoft.public.access
|
|||
|
|||
|
Date
zyus wrote:
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 http://www.accessmonster.com/Uwe/For...ccess/201005/1
|
|
#7
May 25th, 2010, 10:51 AM
posted to microsoft.public.access
|
|||
|
|||
|
Date
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 "PieterLinden via AccessMonster.com" wrote: zyus wrote: 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 http://www.accessmonster.com/Uwe/For...ccess/201005/1 .
|
|
#8
May 25th, 2010, 02:03 PM
posted to microsoft.public.access
|
|||
|
|||
|
Date
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 zyus wrote: 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 "GP George" wrote: That's exactly what Pieter's suggestion should return, so I'm wondering what happened when you tried it? "zyus" wrote in message ... 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" "PieterLinden via AccessMonster.com" wrote: 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 http://www.accessmonster.com/Uwe/For...ccess/201005/1 .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
