Prev: A bug in GUI of creating query with more outer joins betweentwo t
Next: Query Help One-to-Many
From: dimatisah on 24 Mar 2010 14:09
I have an Access database of client information with their birthdate stored
in a field called Birthday, in m/d/yyyy format.

I need a query that will let me find everyone with a birthdate during a
certain date range no matter what year they were born. For example, if I
wanted everyone born during this week, from 3/22-3/28 of any year.

Is there a way to either: A) Change my date fields in the table to only m/d
somehow? or B) Query the month and day only, ignoring the year?

I'd prefer Option B, but am open to anything that might make it work.
Thank you! :)

From: Jerry Whittle on 24 Mar 2010 14:36
PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT ClientInfo.*
FROM ClientInfo
WHERE DatePart("y",[Birthday])
Between DatePart("y",[Start Date])
And DatePart("y",[End Date]);

Make sure that the table name, ClientInfo, it correct. It will prompt you
for dates. If you leave off the year, Access assumes the current year.

One fly in the ointment: Leap Year's will be one day off after the end of
February.

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"dimatisah" wrote:

> I have an Access database of client information with their birthdate stored
> in a field called Birthday, in m/d/yyyy format.
>
> I need a query that will let me find everyone with a birthdate during a
> certain date range no matter what year they were born. For example, if I
> wanted everyone born during this week, from 3/22-3/28 of any year.
>
> Is there a way to either: A) Change my date fields in the table to only m/d
> somehow? or B) Query the month and day only, ignoring the year?
>
> I'd prefer Option B, but am open to anything that might make it work.
> Thank you! :)
>
From: Daryl S on 24 Mar 2010 14:43
Dimatisah -

You can add a field in your query for the birthday based on the current
year, and then add criteria:

BirthDateThisYear:
DateSerial(Year(Date()),Month([Birthdate]),Day([Birthdate]))

Between [StartDate] And [EndDate]

--
Daryl S


"dimatisah" wrote:

> I have an Access database of client information with their birthdate stored
> in a field called Birthday, in m/d/yyyy format.
>
> I need a query that will let me find everyone with a birthdate during a
> certain date range no matter what year they were born. For example, if I
> wanted everyone born during this week, from 3/22-3/28 of any year.
>
> Is there a way to either: A) Change my date fields in the table to only m/d
> somehow? or B) Query the month and day only, ignoring the year?
>
> I'd prefer Option B, but am open to anything that might make it work.
> Thank you! :)
>
 | 
Pages: 1
Prev: A bug in GUI of creating query with more outer joins betweentwo t
Next: Query Help One-to-Many