Post reply

Calculating month

  • March 9, 2006 at 3:37 am

    #70297

    Hi,

    I need to take away [startdate] - [enddate] but I need to get the month and display this in a query in Access.

    Can anyone help me please?

  • March 9, 2006 at 7:04 am

    #625660

    You would want to use the Format function in the query. Try the following:

    MonthName: Format(([startdate]-[enddate]),"mmmm")

    This should give you the full month name. If you just want the month abbreviated use "mmm" instead.

     


    SSgtDevildog

  • March 9, 2006 at 7:06 am

    #625662

    thanks very much. Appreciate it

  • March 9, 2006 at 8:54 am

    #625723

    >>I need to take away [startdate] - [enddate] but I need to get the month and display this in a query in Access.

    I'm Curious to know what your itention is.  I don't think the suggested solution is going to have the effect you expect.  For example, assuming startdate=#1/15/2006# and enddate=#1/17/2006#, evaluate the expression in steps:

          ([startdate]-[enddate]) = -2

          Format(-2, "mmmm") = "December"  (at least it does today)

    Did you by any chance want to get the number of months between the two dates? If so, you may be looking for something more along this line:

        datediff("m", [startdate], [enddate])

    Just curious.

  • March 9, 2006 at 9:05 am

    #625730

    Hi,

    Start date and end date was an example.

    Ok - i've done it wrong. What I'm tring to do is the following.

    In the query builder..         This will get the month from both dates and subtract it

    x: Month([FirstTreatment])

    y: Month([AssesmentDate])

    TimeOnWaitingList: ([x]-[y])

    This works. But if I had the dates

    05/01/2006 as x

    27/04/2005 as y   The difference should be 11 but I get -3

    HELP Please

  • March 9, 2006 at 12:03 pm

    #625778

    what about

    v=year([FirstTreatment])

    w=year([AssesmentDate])

    TimeOnWaitingList: ([x]-[y])+((v-w)*12)

  • March 9, 2006 at 10:38 pm

    #625849

    I'm sorry that I'm so very short of time right now.  I wanted to say more, but here's the core:

    You want DateDiff().  You specify how you want the difference in days, weeks, months, etc.  I believe that you put the earlier date first, but you need at most two tests to see which way around they go.

    Finally figured out why format(-3, "mmmm") gave, and will always give, December.  (I thought it would change as we moved forward in time.)  "-3" is interpreted as a date, and it is 3 days before the reference date, wich is Jan 1, someyear.  (1900?)

    Anyway, hope this helps.  I'll be back in here after a day or two.  Good luck.

  • March 10, 2006 at 2:14 am

    #625872

    Thanks Jo. You're a legend. It works fine displaying the correct months left.

    Regards

    Ritesh

  • March 10, 2006 at 3:42 am

    #625885

    Just a note

    It assumes that FirstTreatment is always later than AssesmentDate.

    Datediff is indeed more appropriate.

  • March 10, 2006 at 7:57 am

    #625975

    What about:

    abs(datediff("m", #3/1/04#, #5/26/05#)) which equals 14 months?

    or

    abs(datediff("m", #5/26/05#, #3/1/04#)) which still equals 14 months?

    By using "abs" (absolute) it does not matter which date is first, all you want is the difference in months anyway.

    [font="Comic Sans MS"]Vic[/font]
    www.vicrauch.com

  • March 10, 2006 at 10:19 am

    #626035

    I am using Val(Format$([SchDate],"mm",0,0)) and is currently working.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply