birthday query

  • Thread starter Thread starter Lynn
  • Start date Start date
L

Lynn

Hello.

I want to search the DOB field to see whose birthday is
for a certain date range. The DOB field is formatted like
12/1/03. Currently I have:

Format([c_and_c_accts]![dob],"mm" & "/" & "dd")

But this formces me to enter a 2-digit day (ie. "03"
instead of just "3" for the third day in the particular
month). It also forces me to enter a year which I don't
care about. Can anyone please help?

Thanks,

Lynn
 
Hello.

I want to search the DOB field to see whose birthday is
for a certain date range. The DOB field is formatted like
12/1/03. Currently I have:

Format([c_and_c_accts]![dob],"mm" & "/" & "dd")

But this formces me to enter a 2-digit day (ie. "03"
instead of just "3" for the third day in the particular
month). It also forces me to enter a year which I don't
care about. Can anyone please help?

Thanks,

Lynn
Click to expand...

A Date/Time value isn't a string; it's a number (a count of days and
fractions of a day since an arbitrary start point). There are a number
of date handling functions to do this. To get this year's birthday
anniversary, use a calculated field

HappyHappy: Year(Date(), Month([DOB]), Day([DOB]))

This field can be formatted however you like, and searched with
criteria such as

BETWEEN Date() AND Date() + 7

to see the birthdays in the upcoming week.
 
Lynn said:
Hello.

I want to search the DOB field to see whose birthday is
for a certain date range. The DOB field is formatted like
12/1/03. Currently I have:

Format([c_and_c_accts]![dob],"mm" & "/" & "dd")

But this formces me to enter a 2-digit day (ie. "03"
instead of just "3" for the third day in the particular
month). It also forces me to enter a year which I don't
care about. Can anyone please help?

Thanks,

Lynn
Click to expand...

Lynn,

As long as the [DOB] field is a valid date, it's formatting doesn't
matter.

To find birthdates in the current month, use, as SQL criteria:
Where Month([DOB]) = Month(Date())

You will not be prompted for any parameter.

To have the user enter (at any time) the month wanted, use:
Where Month([DOB]) = [Enter Month Number]
You will be prompted to enter the month number.
 
(I am assuming that your DOB is a date field)
You are confusing DOB which is a particular event in time with
BirthDay which is a recurring event based on DOB

You will need to convert DOB to BirthDay
One Option, probably not the best is
BirthDay = DateSerial(Year(Date()),Month(DOB),Day(DOB)
Then compare your BirthDay to your range

If BirthDay is between RangeStart and RangeEnd then SendGreetingCard

You should be able to do all this in either code or SQL. though you
will need to get the specific syntax right in both cases.
(I used pseudoCode, for the If Birth... line)

Regards Greg Kraushaar
Wentworth Falls Australia
(Do not email - the reply address is a Spam spoofer)
(If you really must, remove all UCase and numbers)
 
Back
Top