Current Date Anniversary Query

#1 (**permalink**) 04-Nov-2005, 15:19 PM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/6600-current-date-anniversary-query.html

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6600
expression builder no joy..

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

#2 (**permalink**) 04-Nov-2005, 15:19 PM

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" 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6600
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6600
> expression builder no joy..
>
> any advice would be greatly appreciated..
> thanks in advance
> andy
>

#3 (**permalink**) 04-Nov-2005, 15:19 PM

Spot On
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6600
Many many thanks
andy
"Van T. Dinh" 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" wrote in message
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6600
> 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 (**permalink**) 04-Nov-2005, 15:19 PM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6600
>
> I am tring to do is run a query that will extract all the entries that fall
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6600
> 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 (**permalink**) 04-Nov-2005, 15:19 PM

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());
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6600

or

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

James A. Fortune
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6600

#6 (**permalink**) 04-Nov-2005, 15:19 PM

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

"James A. Fortune" 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6600
>> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6600