Extracting and sorting records by year query 1

[PLCBeetle]

I have an Access 2010 database in which I am having issues to isolate the text date from the string and sort the records by the text date.

Table: Movie
Column: Title
Data type: Text

Sample table data:
12 Years A Slave (2013-R)
127 Hours (2010-R)
17 Again (2009-PG13)
21 (2008-PG13)
27 Dresses (2011-PG13)

Here is the query I am working with:
SELECT Movie.Title AS movieyear, movie.title
FROM Movie
WHERE (((movie.title) Like '*20##*'))
ORDER BY movieyear, desc;

The query results I would like to see:
12 Years A Slave (2013-R)
27 Dresses (2011-PG13)
127 Hours (2010-R)
17 Again (2009-PG13)
21 (2008-PG13)

 

[dhookom]

If your data is always as it appears in your samples, I would create a query that creates a Year column:

SELECT Movie.*, Mid([Title],InStr([Title],"(")+1,4) AS [Year]
FROM Movie;

You can then filter and/or sort the query by the Year column.

Duane
Hook'D on Access
MS Access MVP

 

[BigRed1212]

You might also consider expanding Duane's query to extract the rating and then incorporating that into a query intended to normalize your data. One column/field each for title, year, rating, etc. Losing the parens.

Would make things easier to work with.

 

[dhookom]

Good observation BigRed1212.

Duane
Hook'D on Access
MS Access MVP