Next 14 Dates Required

[Mo2783]

Hi all

I hope someone can help, i am trying to get information from 2 tables.

i have a table with Test Information has the date of test field, and another table holds results with a field called ResObtained.

What the user does is enter a date and then the query needs to return the next 14 dates. so for example if the user selected 15/07/2009 then the query will return the next 14 days where it has the data.



example

user selects 15/07/2009

then the query needs to return the next 14 dates, which could be like this

16/07/2009
19/07/2009
20/07/2009
21/07/2009
24/07/2009
29/07/2009
02/08/2009
07/08/2009
10/08/2009
11/08/2009
13/08/2009
14/08/2009
15/08/2009

The results obtained table will hold information like A,A,A+ B,B+ etc.

So a date can have many results. i need to get information where a user selects the date and the next 14 results that are displayed where the results are A regardless of whats after that.

i have the follwoing query

SELECT Results.Result, Test.TestDate AS Isdate
FROM Results INNER JOIN Test ON Results.ResultNumber = Sample.ResultNumber
WHERE (((Results.Result) Like "*A*") AND ((Sample.TestDate) Between [Enter Date] And DateAdd("d",14,[Enter Date])));

Any help would be appreciated.

Thanks.

[jzwp11]

I'm not sure I totally understand what you are after, so please clarify if the approach I suggest doesn't provide a solution.

The WHERE clause of your query restricts the dates between the date entered and 14 days from the date entered. This will not guarantee that you will get the next 14 dates. You might get less or you might get more.

What I would do is to find all of the dates greater than or equal to the date entered and then use another query to get the 14.

First, modify your current query as follows. Please note that I changed the Isdate alias to myDate. Isdate() is a function in Access, so you should not use the same name; it may cause problems.

SELECT Results.Result, Test.TestDate AS myDate
FROM Results INNER JOIN Test ON Results.ResultNumber = Sample.ResultNumber
WHERE (Results.Result Like "*A*") AND (Sample.TestDate >=[Enter Date]) 
ORDER by Testdate desc

Then create this query based on the query above

SELECT TOP 14 Results.Result, Mydate
FROM nameofqueryabove