Results 1 to 11 of 11
Thread: Formula Needed (2002)

20060130, 16:36 #1
 Join Date
 Jan 2002
 Posts
 1,615
 Thanks
 0
 Thanked 1 Time in 1 Post
Formula Needed (2002)
Hi,
I have a form with two text boxes. The user enter the date for the first date of the month into txtStartDate. There is another text box called txtFilter. txtFilter contains the following formula:
=100*Year([txtstartdate])+Month([txtstartdate])+1
The result is used as a filter on a variety of reports. So if the user enters 11/1/05 into txtStartDate, the result in txtFilter result is 200512. This has worked beautifully for 11 months of the year until December. When the user enters 12/1/05 into txtStartDate the filter comes up as 200613 when the answer should be 200513 when the answer should be 200601. I took a stab at fixing the formula using iif but have had no luck. The user needs this correct ASAP so I decided to stop playing with it and ask for help here.
Thanks!
Leesha

20060130, 16:48 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Formula Needed (2002)
Try
=100*Year(DateAdd("yyyy",1,[txtStarDate]))+Month(DateAdd("yyyy",1,[txtStartDate]))

20060130, 17:07 #3
 Join Date
 Jan 2002
 Posts
 1,615
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Formula Needed (2002)
I'm getting an error. I've attached the form for you to see.
Thanks,
Leesha

20060130, 18:31 #4
 Join Date
 Jan 2002
 Posts
 1,615
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Formula Needed (2002)
Went back and looked at the formula again and compared it to what I had and found txtStartDate spelled wrong and after fixing it I'm all set as far as the error goes. The proble is that txtfilter is not returned the correct data now for all months. For example
1/1/05 should = 200502
2/1/05 shoud = 200503
3/1/05 should = 200504 and so on to December
12/1/05 should  200601
What I'm getting now is:
1/1/05 = 200601
2/1/05 = 200602
3/1/05 = 200603
Thanks!
Leesha

20060130, 19:27 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Formula Needed (2002)
Oops! That is my fault. I was replying to you while doing all kind of other things, so I didn't look carefully enough. The "yyyy" in DateAdd adds a year instead of a month. Replace both occurrences of "yyyy" with "m".
Sorry about that!

20060130, 21:52 #6
 Join Date
 Jan 2002
 Posts
 1,615
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Formula Needed (2002)
Thanks Han's! That worked perfectly. I'd have never have gotten that on my own!
Leesha

20060130, 23:50 #7
 Join Date
 Dec 2001
 Location
 Melbourne, Australia
 Posts
 4,607
 Thanks
 0
 Thanked 32 Times in 32 Posts
Re: Formula Needed (2002)
Another way is :
=Format(DateSerial(Year(txtStartDate), Month(txtStartDate)+1, Day(txtStartDate)), "yyyymm")

20060130, 23:53 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Formula Needed (2002)
That produces the same result, but as a text string instead of the number. If the result must be numeric, you can use the Val function:
=Val(Format(...))

20060130, 23:55 #9
 Join Date
 Dec 2001
 Location
 Melbourne, Australia
 Posts
 4,607
 Thanks
 0
 Thanked 32 Times in 32 Posts
Re: Formula Needed (2002)
That's absolutely right, thanks Hans.

20060130, 23:57 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Formula Needed (2002)
As usual, there are many roads leading to Rome (or is that Melbourne?)

20060131, 00:05 #11
 Join Date
 Dec 2001
 Location
 Melbourne, Australia
 Posts
 4,607
 Thanks
 0
 Thanked 32 Times in 32 Posts
Re: Formula Needed (2002)
As long as you have amphibious vehicles, then it's going to be a long rough ride.
But then again for your 41,000 + posts, it's more like all roads lead to Leiden.
Unless you are rich enough to own a Pershing motor yacht, but that is indeed another story.
Back to the subject at hand, I guess we all have our favourite ways of doing things.