Date query...PLEASE HELP..

[rupes_mahal]

Hi...

I have a table which consists of patients details. Each record has a date field which is recorded when a patient is ill.

What I need to do is to write a query which returns records which are 3 years old from the present date. When I click on a command button....I want all the 3 year old records to appear in datasheet view. Also when clicked on another button...the records should appear in form view.
Another condition in the query is to return records which have Part Number as II.


I have build a query, but it does not return any records:

SELECT PremFormRegisterTBL.ReferenceNo, PremFormRegisterTBL.PremiseCode, PremFormRegisterTBL.ReasonforReview, PremFormRegisterTBL.PartNumber, PremFormRegisterTBL.Supercedes, PremFormRegisterTBL.[Prem1(WEFdate)], PremFormRegisterTBL.InvoiceDate, PremFormRegisterTBL.InvoiceNo,
FROM DoctorsDetailsTBL INNER JOIN PremFormRegisterTBL ON DoctorsDetailsTBL.PremiseCode = PremFormRegisterTBL.PremiseCode
WHERE (((PremFormRegisterTBL.[Prem1(WEFdate)])=DateDiff("yyyy",Now(),Now()-3)) AND (((PremFormRegisterTBL.[PartNumber])="II")));


Please help....as I am desparate and can not work any further until I fix this problem...


THANKYOU IN advance..

Ruby

 

[Rich]

WHERE (((PremFormRegisterTBL.[Prem1(WEFdate)])<=DateAdd("yyyy",-3,Date()))

 

[rupes_mahal]

Rich...

It brings back records which are 3 years old by year.....I need to bring back records which are 3 years old from the present date.

For example....i click on the cmdbutton and its brings back 2records dated:

1) 05/10/1998
2) 05/10/1998

Today's date is 05/10/2001.

Minus 3 years from today and it goes back to 05/10/1998 and retrieves these records. This is what I need. So if I use it on a different date....e.g 10/10/2001....it should only bring back the records dated 10/10/1998.

OR

is it possible to bring back records which are 3 years old from the present month.
E.g....Present month is October 2001....so the records retrieved should be all be dated for the month of October 1998. As each month changes, 3 year old records are retrieved according to the month and year (-3).....this method would be better then the above method...if it is possble...


Please help...

Thankyou in advance

Ruby

[This message has been edited by rupes_mahal (edited 10-05-2001).]

 

[rupes_mahal]

hi...

anyone please help with the above problem...I am so struck...

thankyou

ruby

 

[Jack Cowley]

WHERE (((PremFormRegisterTBL.[Prem1(WEFdate)])<=DateAdd("m",-36,Date()))

 

[rupes_mahal]

Jack...

please could you expain the code you have inserted above.....what does it do..?

I inserted the code and I am getting records which are more than 3 years old.

For example:

today date = 06/10/2001

records retrieved by the query are dated between:

01/02/1996 - 05/10/1998

I only want to retrieve records for month of October 1998....reason for this is because these records will be 3 years old from the present month....

please help....thankyou for helping

ruby

 

[raskew]

The previous statement is giving you all records where the date is less than or equal to today's date - 36 months. There's nothing to restrict the returned records to a specific month.

For a little different approach, try creating a new query in Northwind using the following code. When run, you'll be promoted for the number of years prior (use 7 or 8, since this query is based on the Orders table).

PARAMETERS NumYears Short;
SELECT OrderID, OrderDate
FROM Orders
WHERE (((OrderDate) Between DateSerial(Year(Date())-[NumYears],Month(Date()),1) And DateSerial(Year(Date())-[NumYears],Month(Date())+1,0)));

 

[rupes_mahal]

RASKEW...

u are a darling....thankyou so much...brilliant.

Another small question please....The window which pops up asking to input the NumYears......Can I change the top of the window which says at the moment:

Enter Parameter Value

Can I change that...if so How do i change it.....??

thankyou again

ruby

 

[raskew]

Ruby-

In the Help file, search on:

Parameter queries | Create a parameter query that gets criteria from a custom dialog box

Bob