SQL that includes only records with Current Year and earlier

[ilcaa72]

hello

i have a db with a Field named date, formatted 1993-02-11

year start is 1993 till 2017. I want to filter for records that include everything from first year the record started (some records begin in 1993, some in 2000, some in 2008, etc) and include only records that have data within 2017, so return everything that has a start date prior to 2017 AND must have record in 2017.

if record starts in 2002 and has a record in 2017 include, returns all records in between and including
if records starts in 1993 and last recorded record in 2016, DO NOT include, any record returned must have a record in 2017

hope i am making sense, please ask questions so i can clarify... thanks
here is short Pseudo-Code. my date is formatted 2017-02-11

Select * from table1
where MAX(date) = '2017'

 

[jdraw]

Date is a reserved word in Access.

See the Year() function.

 

[plog]

That query isn't going to work. It's gonna take two queries. However you need to tell how your records are related. A record is a single row of information in a table, therefore a record can only be in 1 year. What you meant is realated records with a record in 2017. But what field relates them?

Also, your discussing the format of your date makes me think it is in fact not a date. In design view of your table is the field type of your date a date/time or is it short text?