Filtering second table from the first in a form

[wim1981]

Hi everyone,

i have to make a form with two subforms with each a table with data.
The first table has maximum 3 years.that are shown.
The second table has to have all the records from those 3 years that are stored in the table.

Every year in the first table has a result from several dates in that year.

How can i do this in the sql query? I know to filter with the where only at one year. But it has to be 3 years.

Thanks

 

[arnelgp]

Where DateDiff("yyyy",[yourDateField], Date()) = 3

 

[wim1981]

Where DateDiff("yyyy",[yourDateField], Date()) = 3

I ain't getting any data from my query when i integrate this.

Here's my query

SELECT  dbo_Lan_opleiding.datumopleiding, dbo_Lan_opleiding.omschrijving, dbo_Lan_opleiding.uren
FROM dbo_Lan_opleiding
WHERE  DateDiff("yyyy",dbo_Lan_opleiding.datumopleiding,Date())=3
ORDER BY dbo_Lan_opleiding.datumopleiding DESC;

 

[arnelgp]

do you mean to filter from:
1. last 3 years up to present
2. starting from 3 years ago up to beginning.

case 1:
Where dbo_Lan_opleiding.datumopleiding Between DateDiff("yyyy", dbo_Lan_opleiding.datumopleiding, Date()) And Date()

case 2:
Where dbo_Lan_opleiding.datumopleiding <= DateDiff("yyyy", dbo_Lan_opleiding.datumopleiding, Date())

 

[wim1981]

Hi arnelgp,

i don't want to calculate the days or year between two dates.

I want to display only the records for 3 years.

 

[wim1981]

do you mean to filter from:
1. last 3 years up to present
2. starting from 3 years ago up to beginning.

case 1:
Where dbo_Lan_opleiding.datumopleiding Between DateDiff("yyyy", dbo_Lan_opleiding.datumopleiding, Date()) And Date()

case 2:
Where dbo_Lan_opleiding.datumopleiding <= DateDiff("yyyy", dbo_Lan_opleiding.datumopleiding, Date())

I want to filter all the records from the last 3 years. At te top of the list the present year

 

[arnelgp]

sorry about that, here:

Where dbo_Lan_opleiding.datumopleiding >= DateAdd("yyyy",-3, Date())