Calculating new date and returning dates within a range

[imwalrus38]

I am tring to add a number of years to a dob. I'm 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. I've tried unsuccesfully to use the dateadd function.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 want to determine the date range each time I run the query.

An example would be:

DOB age date I need to follow up
5/15/1935 + 75 = 5/15/2005

Then I request dates between 4/1/05 and 5/30/05 and this date is returned.


I've tried 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'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)
And I've tried the make table query and then run another query from the new table. I can't seemto 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

 

[Jon K]

The DateAdd() function should work. See Query2 in the sample database attached.


For parameter queries, see solutions in this thread:-
http://www.access-programmers.co.uk/forums/showthread.php?t=87102

.

 

[Bat17]

5/15/1935 + 75 = 5/15/2005

Wrong!

5/15/1935 + 75 = 5/15/2010

[DOB]+(75*365.25) = 5/14/2010
DateAdd("yyyy",75,[DOB]) = 5/15/2010

Between #1/1/2010# And #6/1/2010# will find this date

HTH

Peter

 

[The_Doc_Man]

Ah, yes... the old "2+2=5" problem. Good catch, Bat17!

I'll add my 2 cents worth. Another poster had a related problem. Make sure you don't do an apples and oranges comparison. The fields have to be in Date format for the fields to accurately behave like dates. I.e. For Between...And to return the date you wanted. Otherwise these fields could act like strings, which have very different comparison rules. I'm not saying you did this. I'm just reminding you that the problem is a common one. It's a very easy mistake to make.

 

[imwalrus38]

still confused

Thanks everyone
NewDate: DateAdd("yyyy",[Age],[DOB]) fomula works to calculate the date.

I still haven't had anything work for criteria that will allow me to run the querie and select any from and to date and rerun the dates between my parameters. I alway have no data returned. If i taje the criteria out it returns everything. I'm really new to using access and truly don't understand how the functions and criteria work. Does anyone have a suggestion that could explain this aspect in fairly simple language?

Thanks again
J

 

[Bat17]

How are you applying the criteria?
Prompts from the query?
Hard codded in the query?
Pulling the dates from a form?

Can you post your SQL so that we can see what is happening please

Peter

 

[imwalrus38]

I've attached my querie. For viewing

 

[EMP]

To use DateAdd() in a query with parameters, you need to use one of the solutions in the thread linked in Jon K's post. Otherwise, Access just treats the parameter values you entered as two text strings, not two dates and so may return incorrect records or no records.


I have changed the criteria in your query to:

WHERE DateAdd("yyyy",[Age],[DOB]) Between CDATE([Type the beginning date:]) And CDATE([Type the ending date:]);

Now when you enter the parameter values 1/1/2005 and 12/31/2005 when the query is run, record ID 3 with NewDate 7/4/2005 will be returned.

^

 

[Bat17]

Alternatively go Query>Parameters and set up the parameters with the date data type.
Ideally though I would do this from a form so that users could fill in both dates and then click a button to open the query. This is generally easier for the user, it allows you to use default dates and to add 'quick buttons' - Last week, YTD, Last month, etc. to automatically fill in dates for user. It also gives you the opportunity to validate their dates to make sure that they make sense!

Peter

 

[Keith27]

The DateAdd() function should work. See Query2 in the sample database attached.


For parameter queries, see solutions in this thread:-
http://www.access-programmers.co.uk/forums/showthread.php?t=87102

.

Jon K,

Excellent solutions. They help solve a problem in one of my queries.

 

[imwalrus38]

Now when you enter the parameter values 1/1/2005 and 12/31/2005 when the query is run, record ID 3 with NewDate 7/4/2005 will be returned.

^[/QUOTE]


EMP

I ran the query and records 2 and were returned with dates outside 1/1/2005 and 12/31/2005. When I run it through the form it's correct. I'm sorry I'm so dense on this. Why is it that I click on the query and it doesn't work but I click on forms and it does?

 

[Keith27]

I downloaded the database from EMP's post. When I ran the "testind add date" query in it and type 1/1/2005 and then 12/31/2005, only ID 3 was returned. I got the same result on two computers. I couldn't find any form in the database.

Could you compact the database (from the Tools menu), zip it and attach it so that we can have a look at it?

 

[imwalrus38]

File with forms

Here is the file that shows the form.

Thanks

 

[Bat17]

OK that is the DB I posted, If you look ther are two queries,
qryFilteredDates takes its criteria from thew form which sets its data type. When you try to run the query directly and it treats your input as numbers not dates

There are two queries, one that uses the form for parameters and one for direct entry. You need to decide what you are doing with the queries to know which to use

Peter

 

[EMP]

In your query, use one of the solutions in Jon K's link. Then you can always get the correct results no matter how the query is run.

^