Sorting a query/report by text within a queried field

[Clayton]

I am conducting research for a law professor this summer
that involves summarizing copyright cases. I designed a
simple Access database that tracks, among other fields,
the names and citations to the cases I have summarized.
Entries in the case citation field are in a constant
format: [Volume] [Reporter] [Page] ([Court] [Year
decided]), e.g., 323 F.3d 763 (9th Cir. 2003). I would
like to generate a query or report that lists all the
cases I have summarized, and I would like to organize that
document by the year in which each case was decided. Any
suggestions on how to accomplish this short of separating
the case citation field into distinct fields for each
segment of the case citation? I am relatively new to
Access and would greatly appreciate any help I can get.

 

[John Spencer (MVP)]

Well the best way would be to have each piece of data in a separate field.

If you can't do that, you will have to parse the date out. IF your data is
exactly as described, you can grab the year using an expression like the
following.

Mid([YourField],Len([YourField])-5,4)

You can put that in a query as a calculated field and then sort on it.

Field: TheYear: Mid([YourField],Len([YourField])-5,4)

 

[John Spencer (MVP)]

Yes, you can do this with update queries.

On a COPY of your table you can do the following especially if you always have a
space between each element AND never have an extra space.

Add the fields Volume, Reporter, Page, Court and Year to the copy.

Then make an update query does the following;

Update TableCopy
Set Volume = Trim(Left(YourField,InStr(1,YourField," ")))

Then after looking over Volume to see if the contents look correctly
UpDate TableCopy
Set YourField = Trim(Mid(YourField,Instr(1,YourField," ")))

Then
Update TableCopy
Set Reporter = Trim(Left(YourField,InStr(1,YourField," ")))

Repeat the 2nd query above and then
Update TableCopy
Set Page = Trim(Left(YourField,InStr(1,YourField," ")))

etc.

Thanks for the help. Is there an automated way to split
the data in one field into data segments for several
fields, i.e., can I separate my case citation field into
its constituent parts without having to rekey all the
entries?

-----Original Message-----
Well the best way would be to have each piece of data in a separate field.

If you can't do that, you will have to parse the date out. IF your data is
exactly as described, you can grab the year using an expression like the
following.

Mid([YourField],Len([YourField])-5,4)

You can put that in a query as a calculated field and then sort on it.

Field: TheYear: Mid([YourField],Len([YourField])-5,4)

I am conducting research for a law professor this summer
that involves summarizing copyright cases. I designed a
simple Access database that tracks, among other fields,
the names and citations to the cases I have summarized.
Entries in the case citation field are in a constant
format: [Volume] [Reporter] [Page] ([Court] [Year
decided]), e.g., 323 F.3d 763 (9th Cir. 2003). I would
like to generate a query or report that lists all the
cases I have summarized, and I would like to organize that
document by the year in which each case was decided. Any
suggestions on how to accomplish this short of separating
the case citation field into distinct fields for each
segment of the case citation? I am relatively new to
Access and would greatly appreciate any help I can get.

.