# Thread: Formula Needed (2002)

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

2. ## Re: Formula Needed (2002)

Try

=100*Year(DateAdd("yyyy",1,[txtStarDate]))+Month(DateAdd("yyyy",1,[txtStartDate]))

3. ## Re: Formula Needed (2002)

I'm getting an error. I've attached the form for you to see.

Thanks,
Leesha

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

5. ## 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!

6. ## Re: Formula Needed (2002)

Thanks Han's! That worked perfectly. I'd have never have gotten that on my own!

Leesha

7. ## Re: Formula Needed (2002)

Another way is :

=Format(DateSerial(Year(txtStartDate), Month(txtStartDate)+1, Day(txtStartDate)), "yyyymm")

8. ## 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(...))

9. ## Re: Formula Needed (2002)

That's absolutely right, thanks Hans.

10. ## Re: Formula Needed (2002)

As usual, there are many roads leading to Rome (or is that Melbourne?)

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•