Welcome to SPN

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

Sign Up Now!

Rolling dates

Discussion in 'Information Technology' started by Audrey, Nov 4, 2005.

Tags:
  1. Audrey

    Audrey
    Expand Collapse
    Guest

    I need to define a query that will provide 12 months worth of data from the
    date entered in a form. The form field is [firstdate]. The query would
    provide all sales data for 12 months from the date entered (including the
    date entered). The criteria listed below doesn't work.

    Between (DateAdd("yyyy",-1,[Forms]![formname]![firstdate])) And
    ([Forms]![formname]![firstdate])

    Can someone provide the accuate expression?
     
  2. Loading...

    Similar Threads Forum Date
    Rolling the addiction ball Blogs Oct 17, 2015
    General Controlling Kaam (Lust) Hard Talk Apr 8, 2014
    Opinion Does Trolling Kill? Hate Speech & Bullying on the Internet Breaking News Aug 9, 2013
    Imps of the Internet: A Dive into the World of Trolling Information Technology Apr 14, 2013
    Money and Controlling Spending Questions and Answers Nov 18, 2011

  3. Rick B

    Rick B
    Expand Collapse
    Guest

    what do you mean it "doesn't work"?


    --
    Rick B



    "Audrey" <Audrey@discussions.microsoft.com> wrote in message
    news:632743ED-AAEF-445F-A05A-B346BAF57FD4@microsoft.com...
    >I need to define a query that will provide 12 months worth of data from the
    > date entered in a form. The form field is [firstdate]. The query would
    > provide all sales data for 12 months from the date entered (including the
    > date entered). The criteria listed below doesn't work.
    >
    > Between (DateAdd("yyyy",-1,[Forms]![formname]![firstdate])) And
    > ([Forms]![formname]![firstdate])
    >
    > Can someone provide the accuate expression?
     
  4. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    I'm with Rick: what does "doesn't work" mean.

    However, try:

    Between DateAdd("yyyy",-1,CDate([Forms]![formname]![firstdate])) And
    CDate([Forms]![formname]![firstdate])


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Audrey" <Audrey@discussions.microsoft.com> wrote in message
    news:632743ED-AAEF-445F-A05A-B346BAF57FD4@microsoft.com...
    > I need to define a query that will provide 12 months worth of data from

    the
    > date entered in a form. The form field is [firstdate]. The query would
    > provide all sales data for 12 months from the date entered (including the
    > date entered). The criteria listed below doesn't work.
    >
    > Between (DateAdd("yyyy",-1,[Forms]![formname]![firstdate])) And
    > ([Forms]![formname]![firstdate])
    >
    > Can someone provide the accuate expression?
     
  5. Audrey

    Audrey
    Expand Collapse
    Guest

    Well, actually I want the full month not just 12 months from the date. If I
    enter 12/31/2005, I want all sales for December 2005 to all sales up to
    12/31/2005.

    "Rick B" wrote:

    > what do you mean it "doesn't work"?
    >
    >
    > --
    > Rick B
    >
    >
    >
    > "Audrey" <Audrey@discussions.microsoft.com> wrote in message
    > news:632743ED-AAEF-445F-A05A-B346BAF57FD4@microsoft.com...
    > >I need to define a query that will provide 12 months worth of data from the
    > > date entered in a form. The form field is [firstdate]. The query would
    > > provide all sales data for 12 months from the date entered (including the
    > > date entered). The criteria listed below doesn't work.
    > >
    > > Between (DateAdd("yyyy",-1,[Forms]![formname]![firstdate])) And
    > > ([Forms]![formname]![firstdate])
    > >
    > > Can someone provide the accuate expression?

    >
    >
    >
     
  6. Audrey

    Audrey
    Expand Collapse
    Guest

    I mean all sales for December 2004 through December 2005.

    "Audrey" wrote:

    > Well, actually I want the full month not just 12 months from the date. If I
    > enter 12/31/2005, I want all sales for December 2005 to all sales up to
    > 12/31/2005.
    >
    > "Rick B" wrote:
    >
    > > what do you mean it "doesn't work"?
    > >
    > >
    > > --
    > > Rick B
    > >
    > >
    > >
    > > "Audrey" <Audrey@discussions.microsoft.com> wrote in message
    > > news:632743ED-AAEF-445F-A05A-B346BAF57FD4@microsoft.com...
    > > >I need to define a query that will provide 12 months worth of data from the
    > > > date entered in a form. The form field is [firstdate]. The query would
    > > > provide all sales data for 12 months from the date entered (including the
    > > > date entered). The criteria listed below doesn't work.
    > > >
    > > > Between (DateAdd("yyyy",-1,[Forms]![formname]![firstdate])) And
    > > > ([Forms]![formname]![firstdate])
    > > >
    > > > Can someone provide the accuate expression?

    > >
    > >
    > >
     
  7. Van T. Dinh

    Van T. Dinh
    Expand Collapse
    Guest

    Slightly strange naming convention: the way you use it, "FirstDate" should
    be named "LastDate".

    Do you have non-zero time component in your date Field? If you do, your
    criteria translate to midnight of 12/31/2005 and sales on 12/31/2005 (most
    likely to be after midnight) will NOT be included.

    Try

    >= DateAdd("d", 1, DateAdd("yyyy",-1,[Forms]![formname]![firstdate]))

    And < DateAdd("d", 1, [Forms]![formname]![firstdate])

    The above will eliminates sales on 12/31/2004 and includes sales for the
    whole day 12/31/2005.

    --
    HTH
    Van T. Dinh
    MVP (Access)



    "Audrey" <Audrey@discussions.microsoft.com> wrote in message
    news:1E1754A4-840E-4478-B967-AA9D10503B58@microsoft.com...
    > Well, actually I want the full month not just 12 months from the date. If
    > I
    > enter 12/31/2005, I want all sales for December 2005 to all sales up to
    > 12/31/2005.
    >
     
  8. Audrey

    Audrey
    Expand Collapse
    Guest

    Well, how about if I use the form field (it happens to be the first date on
    the form and if I add another field, it will just confuse them) and disregard
    the actual date - just use the month and year portion of the field for the
    query?

    "Van T. Dinh" wrote:

    > Slightly strange naming convention: the way you use it, "FirstDate" should
    > be named "LastDate".
    >
    > Do you have non-zero time component in your date Field? If you do, your
    > criteria translate to midnight of 12/31/2005 and sales on 12/31/2005 (most
    > likely to be after midnight) will NOT be included.
    >
    > Try
    >
    > >= DateAdd("d", 1, DateAdd("yyyy",-1,[Forms]![formname]![firstdate]))

    > And < DateAdd("d", 1, [Forms]![formname]![firstdate])
    >
    > The above will eliminates sales on 12/31/2004 and includes sales for the
    > whole day 12/31/2005.
    >
    > --
    > HTH
    > Van T. Dinh
    > MVP (Access)
    >
    >
    >
    > "Audrey" <Audrey@discussions.microsoft.com> wrote in message
    > news:1E1754A4-840E-4478-B967-AA9D10503B58@microsoft.com...
    > > Well, actually I want the full month not just 12 months from the date. If
    > > I
    > > enter 12/31/2005, I want all sales for December 2005 to all sales up to
    > > 12/31/2005.
    > >

    >
    >
    >
     
  9. Van T. Dinh

    Van T. Dinh
    Expand Collapse
    Guest

    Audrey

    The description is still very confusing: it sounds like you want 13 months
    and not 12 months' worth of sales. If you can't describe what you want, we
    can't provide accurate suggestions.

    In addition, you are not answering our questions for additional info., e.d.
    does your sale date have non-zero time component?

    Post you set-up (details of Table(s), Form, etc...), *exactly* what you
    want, provide a sample set of data (relelvant to the boundary conditions)
    and the set of results you want related to the sample set of data.

    --
    HTH
    Van T. Dinh
    MVP (Access)



    "Audrey" <Audrey@discussions.microsoft.com> wrote in message
    news:95A8F82D-610E-4511-906D-1133A9074020@microsoft.com...
    > Well, how about if I use the form field (it happens to be the first date
    > on
    > the form and if I add another field, it will just confuse them) and
    > disregard
    > the actual date - just use the month and year portion of the field for the
    > query?
    >
     
  10. Audrey

    Audrey
    Expand Collapse
    Guest

    After going back to the manager that requested the report, he has decided
    that he would like to enter the date range himself (firstdate and
    seconddate), which is what we have on several other reports anyway. It did
    sound like he wanted 13 months instead of 12. Now he will know exactly what
    he's getting. Problem resolved.

    Thank you for your patience !

    "Van T. Dinh" wrote:

    > Audrey
    >
    > The description is still very confusing: it sounds like you want 13 months
    > and not 12 months' worth of sales. If you can't describe what you want, we
    > can't provide accurate suggestions.
    >
    > In addition, you are not answering our questions for additional info., e.d.
    > does your sale date have non-zero time component?
    >
    > Post you set-up (details of Table(s), Form, etc...), *exactly* what you
    > want, provide a sample set of data (relelvant to the boundary conditions)
    > and the set of results you want related to the sample set of data.
    >
    > --
    > HTH
    > Van T. Dinh
    > MVP (Access)
    >
    >
    >
    > "Audrey" <Audrey@discussions.microsoft.com> wrote in message
    > news:95A8F82D-610E-4511-906D-1133A9074020@microsoft.com...
    > > Well, how about if I use the form field (it happens to be the first date
    > > on
    > > the form and if I add another field, it will just confuse them) and
    > > disregard
    > > the actual date - just use the month and year portion of the field for the
    > > query?
    > >

    >
    >
    >
     

Share This Page