Find Year in a Range Of Years

[HeyMoe]

I'm trying to develop a database to catalogue LPs/CDs.


One thing I would like to do is to specify which decade a LP was released.

So I would have a simple table with
Decade/StartYear/EndYear.
60's /1960 /1969
70's /1970 /1979
etc. You get the gist.

In the main table, there would be a column with release date in a Month/Year format. eg. June-1975
I would like to then create a query that would look up the decade based on this release date.
Would this be difficult to do?

Thanks.

[Welshgasman]

No. Pretty simple really.
I would use a DlookUp() with the value from the YEAR() function on your date. Use Between StartYear and EndYear as the criteria.

[Bob Fitz]

I would have a field for the month and a field for the year. They can be concatenated in forms/reports if you wish.

I would like to then create a query that would look up the decade based on this release date.

It is usually best to perform a calculation whenever and wherever it is required, rather than saving it to a field in a table. A calculation like this would usually be done n the query used as the Record Source of the form/report.
Just as a matter of interest, how will you distinguish between say, the 1920's and the 2020's ?

[HeyMoe]

No. Pretty simple really.
I would use a DlookUp() with the value from the YEAR() function on your date. Use Between StartYear and EndYear as the criteria.

Thanks Welshgasman.
Correct me if I'm wrong, but DlookUp() is a function used in VBA.
How would you do this using only SQL? (If it's possible of course.)

Cheers.

[Welshgasman]

Thanks Welshgasman.
Correct me if I'm wrong, but DlookUp() is a function used in VBA.
How would you do this using only SQL? (If it's possible of course.)

Cheers.

To use a query join, I believe you would need a record for each year?

60's 1960
60's 1961

etc

You can use DLookUp in a query, but not recommended for large datasets.
I doubt your record collection comes into that category?