how to create a date based alert?

[dapper]

hi!

I'm a bit of a n00b so please bear with me.

I've set up a fairly functional database at the moment, and I've figured out relationships n'all between my three table thingies.

What I thought would be nice though, is if I could get it to alert me somehow around 3-4 days before a persons birthday. Currently I have DOB entered as a regular 'short date'. I thought it'd be quite simple to do but I'm not seeing anything that might do it.

Thanks!

 

[WayneRyan]

dapper,

Make a query that has the fields that you want from the table. Then add
one new field:

Expr: Between Format([DOB], "mmmdd") And Format(Date(), "mmmdd")

SELECT *
FROM tblEmployees
WHERE (((Format([DOB],"mmmdd")) Between Format(Date(),"mmmdd") And Format(Date()+3,"mmmdd")));

Wayne

 

[raskew]

Wayne,

Tried your code (modified) against Northwind's Employees table:

SELECT
    *
FROM
   Employees
WHERE
   (((Format([BirthDate],"mmmdd")) Between Format(Date(),"mmmdd") 
AND
   Format(Date()+60,"mmmdd")));

...and it returned some unexpected results.

Coding it thusly seems to return the desired output---

SELECT
    DateSerial(Year(Date()),Month([BirthDate]),Day([BirthDate])) AS Birthday
  , *
FROM
   Employees
WHERE
   (((DateSerial(Year(Date()),Month([BirthDate]),Day([BirthDate]))) Between Date() 
AND
   Date()+60));

Best wishes, Bob

 

[WayneRyan]

Good catch Bob!

With my sample data (AND a 3-day window), I didn't span any months.
What mine really needed was "mmdd" to get the numeric month. But,
mine will still have trouble at the end of the year.

Your approach seems better, but I've never seen the syntax:

(a,b,c) Between Date() And Date() + 60

I'll research it, or let me know about the syntax.

Wayne

 

[raskew]

Wayne -

(a,b,c) creates the employees birthday this year. Since the goal

is to view upcoming birthdays and the nearest to today is Janet

Leverlings on 30-Aug-04 the

WHERE (birthday calc) BETWEEN Date() and Date()+60
... translates to
WHERE (birthday calc) BETWEEN #18-Jul-2004# and #16-Sep-2004#

and would capture the 30-Aug-04 event.

Does it not work for you?

Bob

 

[WayneRyan]

Sorry Bob,

I see it now, you're just putting their birthday into THIS year. I'm not
normally this slow.

Thanks,
Wayne

 

[raskew]

Wayne-

Urgent help required!!

When I lived in Camarillo many, many years ago there

was an urban legend that residing in close proximity to

the state mental hospital located there (is it still operating?)

could, in later years, have subtle effects on ones

mental acuity. Do you know if this has since been proven

and, more importantly, have they found a cure?

Best wishes, Bob

 

[WayneRyan]

That is true Bob!

But not in this case.

I've been cleaning the garage, hanging ceiling fans, golfing, and fixing
the car (and its hot!). Just need to go drink some beers and shoot
pool in a nice air-conditioned bar.

btw, The hospital is now a college! Cal State Univ. Channel Islands (CSUCI).

See ya,
Wayne

 

[nhk]

hye Wayne and Raskew,

i need your help..
i do not understand how to do the alert message..
can both of you explain it to me..
for example, i want to have alert message when the date_return is one day before..

your help is much appreciate..
thankz in advance..