Results 1 to 11 of 11
  1. #1
    Silver Lounger
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula Needed (2002)

    Try

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

  3. #3
    Silver Lounger
    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

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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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!

  6. #6
    Silver Lounger
    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

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Formula Needed (2002)

    Another way is :

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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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(...))

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Formula Needed (2002)

    That's absolutely right, thanks Hans.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula Needed (2002)

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

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 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.

Posting Permissions

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