Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Extracting and sorting records by year query 1

Status
Not open for further replies.

PLCBeetle

Programmer
Sep 30, 2008
19
US
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)
 
If your data is always as it appears in your samples, I would create a query that creates a Year column:

SQL:
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
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top