Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Please help with Date Subtraction

Discussion in 'Information Technology' started by Adriana, Jul 28, 2006.

  1. Adriana

    Adriana
    Expand Collapse
    Guest

    I need a query to give me the current year and current month minus one year,
    so June 2006 to June 2005, etc. One field for the year in the query now says:
    Field Column: Year: Format([Release Date:],"yyyy")
    Total: Group By, Ascending
    Criteria says: Format(Now(),"yyyy")

    How do I do this and is it possible?
     
  2. Loading...

    Similar Threads Forum Date
    General Help! Looking for a good clairvoyant/card reader......any suggestions? Please Blogs Jan 25, 2016
    Please help Questions and Answers Nov 1, 2013
    Please Help! What is the Name of the Narrator/Raagi? Gurbani Download Oct 16, 2012
    Muslim Girl-Sikh Boy Marriage Problems- Please Help! Love & Marriage Oct 12, 2012
    Help Please :/ Relationships Apr 19, 2011

  3. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    Adriana wrote:
    > I need a query to give me the current year and current month minus
    > one year, so June 2006 to June 2005, etc. One field for the year in
    > the query now says: Field Column: Year: Format([Release
    > Date:],"yyyy")
    > Total: Group By, Ascending
    > Criteria says: Format(Now(),"yyyy")
    >
    > How do I do this and is it possible?


    WHERE [Release Date] >= DateSerial(Year(Date())-1, Month(Date()), 1)
    AND [Release Date] < DateSerial(Year(Date()), Month(Date()) + 1, 1)

    --
    Rick Brandt, Microsoft Access MVP
    Email (as appropriate) to...
    RBrandt at Hunter dot com
     
  4. Adriana

    Adriana
    Expand Collapse
    Guest

    I copied your entire statement you gave me in the criteria field and it asks
    me for a release date parameter value. If I don't enter one, I'll get no
    results, if I enter 06/2006, I'll get all records dating back to 1999.
    Should I change the Total field to "Where" or "Expression" instead of "Group
    By"? Did you mean to say I should copy that whole statement in the criteria
    field?

    "Rick Brandt" wrote:

    > Adriana wrote:
    > > I need a query to give me the current year and current month minus
    > > one year, so June 2006 to June 2005, etc. One field for the year in
    > > the query now says: Field Column: Year: Format([Release
    > > Date:],"yyyy")
    > > Total: Group By, Ascending
    > > Criteria says: Format(Now(),"yyyy")
    > >
    > > How do I do this and is it possible?

    >
    > WHERE [Release Date] >= DateSerial(Year(Date())-1, Month(Date()), 1)
    > AND [Release Date] < DateSerial(Year(Date()), Month(Date()) + 1, 1)
    >
    > --
    > Rick Brandt, Microsoft Access MVP
    > Email (as appropriate) to...
    > RBrandt at Hunter dot com
    >
    >
    >
     
  5. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    Adriana wrote:
    > I copied your entire statement you gave me in the criteria field and
    > it asks me for a release date parameter value. If I don't enter one,
    > I'll get no results, if I enter 06/2006, I'll get all records dating
    > back to 1999. Should I change the Total field to "Where" or
    > "Expression" instead of "Group By"? Did you mean to say I should
    > copy that whole statement in the criteria field?


    If your field name actually contains a colon then it is because I left that out.
    Best practice for field names is no spaces and letters only.

    --
    Rick Brandt, Microsoft Access MVP
    Email (as appropriate) to...
    RBrandt at Hunter dot com
     
  6. Adriana

    Adriana
    Expand Collapse
    Guest

    Didn't work unfortunately. Someone else gave me the statement "Between
    Now()-365 And Now()" and this doesn't produce any results in the datasheet
    view. Any other ideas?


    "Rick Brandt" wrote:

    > Adriana wrote:
    > > I copied your entire statement you gave me in the criteria field and
    > > it asks me for a release date parameter value. If I don't enter one,
    > > I'll get no results, if I enter 06/2006, I'll get all records dating
    > > back to 1999. Should I change the Total field to "Where" or
    > > "Expression" instead of "Group By"? Did you mean to say I should
    > > copy that whole statement in the criteria field?

    >
    > If your field name actually contains a colon then it is because I left that out.
    > Best practice for field names is no spaces and letters only.
    >
    > --
    > Rick Brandt, Microsoft Access MVP
    > Email (as appropriate) to...
    > RBrandt at Hunter dot com
    >
    >
    >
     
  7. Ron2006

    Ron2006
    Expand Collapse
    Guest

    What is the datatype and format of the field that you are comparing
    against.

    Also you probably will want to use

    Date-365 And Date

    Now() automatically includes a time constraint. Also this method will
    not account for leap year.

    Ron.
     
  8. Adriana

    Adriana
    Expand Collapse
    Guest

    In the original table, "Release Date:" field is a general date data type,
    which of course would include day, month and year. For this query, they
    separated it by using "Year: Format([Release Date:],"yyyy")" in the Field Row
    description, nothing in the table row, and Group By and Ascending sorted. In
    the criteria, it's:
    Format(Now(),"yyyy"). I don't think any criteria should be placed in the
    month columns (there are two), it should be placed in the year to go back one
    year unless I'm wrong.

    "Ron2006" wrote:

    > What is the datatype and format of the field that you are comparing
    > against.
    >
    > Also you probably will want to use
    >
    > Date-365 And Date
    >
    > Now() automatically includes a time constraint. Also this method will
    > not account for leap year.
    >
    > Ron.
    >
    >
     
  9. Ron2006

    Ron2006
    Expand Collapse
    Guest

    With what you have described it says that it will select all records
    that have a year in the field "Release Date:" {with a colon - which is
    unusual but apparently how they defined it} that is equal to 2006. An
    acceptable format but is it what you want?

    Is there any other criteria in any of the other columns dealing with
    date?

    Note:
    I have never seen a field defined with a colon in the name. Something
    to be avoided as you define your tables.

    Ron
     
  10. Ron2006

    Ron2006
    Expand Collapse
    Guest

    Another way to format the top and the bottom line for that matter is:

    Top line: Year([Release Date:])

    Bottom line: Year(Date())

    To avoid confusion in my own mind, if I am trying to compare dates
    (without time) I always use Date(). Unless you are supecific now() will
    include the time automatically.

    Ron
     
  11. Adriana

    Adriana
    Expand Collapse
    Guest

    I know that colons should not be placed in filed names and unfornately, this
    was defined by another user when they built this db. There are two other
    columns dealing with date: 2 month columns One that says "M1:
    Format([Release Date:],"mm")" - this lists what numbered month it is (Jan is
    01, Feb is 02, etc.) and a month column that lists the month by name when
    query is run: Month: Format([Release Date:],"mmmm"). So essentially the
    Release Date from the table is in general date format for its data type and
    in this query it is separated.
    What I need is the pivot chart to go back one year from it's present month
    and year. From June 2006 to June 2005, Aug 2006 to Aug 2005, etc. I just
    figured that the year criteria column is all I needed to mess with.

    My other problem is getting the pivot chart to translate to a MS graph
    chart. The specific suppliers I've chosen in the pivot chart is not what's
    coming out on the graph chart, it's showing them all. This must be something
    in the row source in properties - a separate hurdle I'm battling.

    "Ron2006" wrote:

    > With what you have described it says that it will select all records
    > that have a year in the field "Release Date:" {with a colon - which is
    > unusual but apparently how they defined it} that is equal to 2006. An
    > acceptable format but is it what you want?
    >
    > Is there any other criteria in any of the other columns dealing with
    > date?
    >
    > Note:
    > I have never seen a field defined with a colon in the name. Something
    > to be avoided as you define your tables.
    >
    > Ron
    >
    >
     
  12. Ron2006

    Ron2006
    Expand Collapse
    Guest

    I don't think that working with just the year will do it since you are
    trying to go back 12 months not just get this year or last year.

    One other question: you said the datatype for [Release Date:] was
    timedate. What is the specific format? is it shortdate or blank(the
    default)

    I would suggest taking the criteria off of the year, (you may or may
    not wish the year field to still be there depending on your graph.
    What I would suggest is create a column with the [Release Date:] in the
    top row and
    the range/criteria be:

    between dateadd("y",-1,date()) and date()


    This will give you today's date and today's date less 1 year


    I think everything else should be left alone, just to see if you are
    getting the records you want.

    Ron
     
  13. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    Adriana wrote:
    > Didn't work unfortunately. Someone else gave me the statement
    > "Between Now()-365 And Now()" and this doesn't produce any results in
    > the datasheet view. Any other ideas?


    Please define "didn't work". You can see what is happening we cannot. I assure
    that if you apply that criteria to a DateTime field it will do what you want.

    --
    Rick Brandt, Microsoft Access MVP
    Email (as appropriate) to...
    RBrandt at Hunter dot com
     
  14. Adriana

    Adriana
    Expand Collapse
    Guest

    I took out the other date fields, dragged the Release Date: field in and used
    the expression you supplied, but it produced no data in the datasheet view.
    Yes, the datatype for Release Date is general date/time which includes date
    and time (the very top selection when you go into the bottom window of design
    view and choose the format of date that is desired.

    "Ron2006" wrote:

    > I don't think that working with just the year will do it since you are
    > trying to go back 12 months not just get this year or last year.
    >
    > One other question: you said the datatype for [Release Date:] was
    > timedate. What is the specific format? is it shortdate or blank(the
    > default)
    >
    > I would suggest taking the criteria off of the year, (you may or may
    > not wish the year field to still be there depending on your graph.
    > What I would suggest is create a column with the [Release Date:] in the
    > top row and
    > the range/criteria be:
    >
    > between dateadd("y",-1,date()) and date()
    >
    >
    > This will give you today's date and today's date less 1 year
    >
    >
    > I think everything else should be left alone, just to see if you are
    > getting the records you want.
    >
    > Ron
    >
    >
     
  15. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    Adriana wrote:
    > I took out the other date fields, dragged the Release Date: field in
    > and used the expression you supplied, but it produced no data in the
    > datasheet view. Yes, the datatype for Release Date is general
    > date/time which includes date and time (the very top selection when
    > you go into the bottom window of design view and choose the format of
    > date that is desired.


    Post the entire SQL statement. Clearly the trees are obscuring the forest.

    --
    Rick Brandt, Microsoft Access MVP
    Email (as appropriate) to...
    RBrandt at Hunter dot com
     

Share This Page