date ranges from a calculated date field

[John Feeley]

I am tring to add a number of years to a dob. im doing this by adding my

date+years*365.26 I get a string of numbers. I then convert the number in

the next column to actual date again. I'm getting the correct date. Now I

want my criteria on that column to allow me to return only date in a given

to from period of my choosing. I try the between_and functions but nothing

is returned. I'm guessing it's because the column is still a calculation and

not a true date.

I'm trying to add my dob column to a column that has a certian number of

years (each record is differrent) and then add a criteria to my result

column that allows me to retrieve only records for dates within a date date

range. I want to determine the date range each time I run the query.

I've tried: Adding a column to my query to convert the number string back to

date format (criteria failed to return a result). Adding a second column

that ='s my converted number to date column (criteria failed to return a

result)

make table query and then run another query from the new table. I can't seem

to make this happen. I think it's not recognizing my data as a date because

it isn't returning any data.

Is ther an easier way to: Add a number of years to a date and have it return

a date and not the number string?

If not is there a better way to convert the string to a date?

Is there a way to get my criteria to recognize the date and return date

between my begin and end dates?



Thanks

John

 

[John Vinson]

I am tring to add a number of years to a dob. im doing this by adding my

date+years*365.26 I get a string of numbers.

Try instead

DateAdd("yyyy", [tears], [DOB])

It correctly handles leapyears and calculates an exact date.

I then convert the number in

the next column to actual date again. I'm getting the correct date.

Close... but not exact. Years are not 365.26 days long, except on
average (in fact they're closer to 365.2624... days); a person's 50th
birthday is not on a fractional day as your algorithim implies.

Now I
want my criteria on that column to allow me to return only date in a given
to from period of my choosing. I try the between_and functions but nothing
is returned. I'm guessing it's because the column is still a calculation and
not a true date.

DateValue() would return a date - but the DateAdd() function returns a
date natively without difficulty.

I'm trying to add my dob column to a column that has a certian number of
years (each record is differrent) and then add a criteria to my result
column that allows me to retrieve only records for dates within a date date
range. I want to determine the date range each time I run the query.

I've tried: Adding a column to my query to convert the number string back to
date format (criteria failed to return a result). Adding a second column
that ='s my converted number to date column (criteria failed to return a
result)

You can put a criterion of

BETWEEN [Enter start date:] AND [Enter end date:]

under the calculated DateAdd() field. It'll work.

John W. Vinson[MVP]