breaking down (because of) a date

[davesmith]

I have a table, this contains personal details such as name, address, and Date of birth, this is what my problem is.

In the system i'm writing i want to have a screen which pops up after login with all a member of staffs' clients who have a birthday in say the next 21 days.

In access i managed to do this by splitting away the day, month and then adding the current year when checking.

any ideas, from a dob considering if they have a birthday in the next 21 day ie. its the 22nd july, show everyone who's birthday is between 22nd july and 12th august!!!

Any pointers appreciated
Cheers and Stuff

DaveSmith

 

[shaddow]

This rquires vbscript

currentdate=Date
'add 21 days to the currendate
finaldate=DateAdd("d", 21, currentdate)
querry="select * from YourTable where YourDates between #"&currentdate"# and #"&finaldate&"#"

here some help...

DateAdd(interval, number, date)

Arguments
interval 
Required. String expression that is the interval you want to add. See Settings section for values. 
number 
Required. Numeric expression that is the number of interval you want to add. The numeric expression can either be positive, for dates in the future, or negative, for dates in the past. 
date 
Required. Variant or literal representing the date to which interval is added. 
Settings
The interval argument can have the following values:

yyyy    - Year 
q       - Quarter 
m       - Month 
y       - Day of year 
d       - Day 
w       - Weekday 
ww      - Week of year 
h       - Hour 
n       - Minute 
s       - Second 

Remarks
You can use the DateAdd function to add or subtract a specified time interval from a date. For example, you can use DateAdd to calculate a date 30 days from today or a time 45 minutes from now. To add days to date, you can use Day of Year ("y"), Day ("d"), or Weekday ("w").

The DateAdd function won't return an invalid date. The following example adds one month to January 31: 

NewDate = DateAdd("m", 1, "31-Jan-95")
In this case, DateAdd returns 28-Feb-95, not 31-Feb-95. If date is 31-Jan-96, it returns 29-Feb-96 because 1996 is a leap year.

If the calculated date would precede the year 100, an error occurs.

If number isn't a Long value, it is rounded to the nearest whole number before being evaluated.

________
George, M

 

[davesmith]

ok, i think i've got how to get the start/end date etc..

main problem is that i'm working with dates of birth.

14/10/78, 12/10/56, 04/03/80

i have also got to take into account these are pre 2000?
The info is from a different system which i am taking selective data from.

Meaning all i want to take into account is the day and month.

Someone has told me of a dayofyear function.

This meaning i could take todays date, ie 1.feb = 32 etc
then i could take these other dates and do the same, meaning whichever year, i'm quids in....

I just can't get the thing to work!!!

Any ideas on this or any other way? Cheers and Stuff

DaveSmith

 

[shaddow]

U need to get only the day or the year

day=Day(mydate)
year=Year(mydate)

and then store this to your database ________
George, M