Welcome to SPN

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

Sign Up Now!

Current Date Anniversary Query

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

  1. AndyH

    AndyH
    Expand Collapse
    Guest

    Hi
    I have built a very simple small db that is basically a way of recording
    events in history.
    What I have is the following fields: category, date, heading and details

    I am tring to do is run a query that will extract all the entries that fall
    on today's date ie all the 3/11.
    ie pull 3/11/2004 3/11/1999 3/11/2001 etc and leave the rest. The idea is to
    extract all the current day anniversary events
    I am a bit stuck what criteria to write in the query design. Tried the
    expression builder no joy..

    any advice would be greatly appreciated..
    thanks in advance
    andy
     
  2. Loading...


  3. Van T. Dinh

    Van T. Dinh
    Expand Collapse
    Guest

    You can use the criterion like:

    ..... WHERE Format([DateField], "mmdd") = Format(Date(), "mmdd")

    or alternatively:

    .... WHERE (Month([DateField]) = Month(Date()))
    AND (Day([DateField]) =Day(Date()))

    --
    HTH
    Van T. Dinh
    MVP (Access)



    "AndyH" <andy@lineone.net> wrote in message
    news:uJoMX$H4FHA.1420@TK2MSFTNGP09.phx.gbl...
    > Hi
    > I have built a very simple small db that is basically a way of recording
    > events in history.
    > What I have is the following fields: category, date, heading and details
    >
    > I am tring to do is run a query that will extract all the entries that
    > fall on today's date ie all the 3/11.
    > ie pull 3/11/2004 3/11/1999 3/11/2001 etc and leave the rest. The idea is
    > to extract all the current day anniversary events
    > I am a bit stuck what criteria to write in the query design. Tried the
    > expression builder no joy..
    >
    > any advice would be greatly appreciated..
    > thanks in advance
    > andy
    >
     
  4. AndyH

    AndyH
    Expand Collapse
    Guest

    Spot On
    Many many thanks
    andy
    "Van T. Dinh" <VanThien.Dinh@discussions.microsoft.com> wrote in message
    news:uq6rwDI4FHA.3036@TK2MSFTNGP15.phx.gbl...
    > You can use the criterion like:
    >
    > .... WHERE Format([DateField], "mmdd") = Format(Date(), "mmdd")
    >
    > or alternatively:
    >
    > ... WHERE (Month([DateField]) = Month(Date()))
    > AND (Day([DateField]) =Day(Date()))
    >
    > --
    > HTH
    > Van T. Dinh
    > MVP (Access)
    >
    >
    >
    > "AndyH" <andy@lineone.net> wrote in message
    > news:uJoMX$H4FHA.1420@TK2MSFTNGP09.phx.gbl...
    >> Hi
    >> I have built a very simple small db that is basically a way of recording
    >> events in history.
    >> What I have is the following fields: category, date, heading and details
    >>
    >> I am tring to do is run a query that will extract all the entries that
    >> fall on today's date ie all the 3/11.
    >> ie pull 3/11/2004 3/11/1999 3/11/2001 etc and leave the rest. The idea is
    >> to extract all the current day anniversary events
    >> I am a bit stuck what criteria to write in the query design. Tried the
    >> expression builder no joy..
    >>
    >> any advice would be greatly appreciated..
    >> thanks in advance
    >> andy
    >>

    >
    >
     
  5. Sprinks

    Sprinks
    Expand Collapse
    Guest

    Andy,

    Use two calls to the DatePart function to match the month and day to today's
    date.

    SELECT YourTable.*
    FROM YourTable
    WHERE ((DatePart("m",[YourDateField])=DatePart("m",Date()) And
    DatePart("d",[YourDateField])=DatePart("d",Date())));

    Sprinks

    "AndyH" wrote:

    > Hi
    > I have built a very simple small db that is basically a way of recording
    > events in history.
    > What I have is the following fields: category, date, heading and details
    >
    > I am tring to do is run a query that will extract all the entries that fall
    > on today's date ie all the 3/11.
    > ie pull 3/11/2004 3/11/1999 3/11/2001 etc and leave the rest. The idea is to
    > extract all the current day anniversary events
    > I am a bit stuck what criteria to write in the query design. Tried the
    > expression builder no joy..
    >
    > any advice would be greatly appreciated..
    > thanks in advance
    > andy
    >
    >
    >
     
  6. James A. Fortune

    James A. Fortune
    Expand Collapse
    Guest

    AndyH wrote:
    > Hi
    > I have built a very simple small db that is basically a way of recording
    > events in history.
    > What I have is the following fields: category, date, heading and details
    >
    > I am tring to do is run a query that will extract all the entries that fall
    > on today's date ie all the 3/11.
    > ie pull 3/11/2004 3/11/1999 3/11/2001 etc and leave the rest. The idea is to
    > extract all the current day anniversary events
    > I am a bit stuck what criteria to write in the query design. Tried the
    > expression builder no joy..
    >
    > any advice would be greatly appreciated..
    > thanks in advance
    > andy
    >
    >


    Events that happened on today's date:

    SELECT EventName, EventDate FROM MyTable WHERE Month(EventDate) =
    Month(Date()) AND Day(EventDate) = Day(Date());

    or

    SELECT EventName, EventDate FROM MyTable WHERE Format(EventDate,
    '\.mmdd') = Format(Date(), '\.mmdd');

    James A. Fortune
     
  7. AndyH

    AndyH
    Expand Collapse
    Guest

    Thanks everyone for your help... very much appreciated..
    andy


    "James A. Fortune" <jimfortune@compumarc.com> wrote in message
    news:OmKHpLI4FHA.3540@TK2MSFTNGP10.phx.gbl...
    > AndyH wrote:
    >> Hi
    >> I have built a very simple small db that is basically a way of recording
    >> events in history.
    >> What I have is the following fields: category, date, heading and details
    >>
    >> I am tring to do is run a query that will extract all the entries that
    >> fall on today's date ie all the 3/11.
    >> ie pull 3/11/2004 3/11/1999 3/11/2001 etc and leave the rest. The idea is
    >> to extract all the current day anniversary events
    >> I am a bit stuck what criteria to write in the query design. Tried the
    >> expression builder no joy..
    >>
    >> any advice would be greatly appreciated..
    >> thanks in advance
    >> andy

    >
    > Events that happened on today's date:
    >
    > SELECT EventName, EventDate FROM MyTable WHERE Month(EventDate) =
    > Month(Date()) AND Day(EventDate) = Day(Date());
    >
    > or
    >
    > SELECT EventName, EventDate FROM MyTable WHERE Format(EventDate, '\.mmdd')
    > = Format(Date(), '\.mmdd');
    >
    > James A. Fortune
     

Share This Page