Sorting a table by concatenating several fields in the same table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am creating a database to store information about printed media. For
example, a single table with separate fields for title, author, publisher,
citations, date of publishing, etc. I want the user to choose what fields to
sort with, so that he can select a first field to sort by and a second field
to further sort any identical entries in the first field. Ideally, I want to
allow the user to select up to three fields for sorting. I would prefer to
accomplish this using VB. I am using Access 2002.

I have had no problem sorting the data with one selected field by making a
query with the Order By statement. But I have not been able to implement the
sorting with multiple fields. I have an idea that I believe would allow me
to continue using the Order By statement and still let the user define up to
3 fields (although I have not been able to implement it). I would create a
separate field for sorting that would be left blank. When the user selects
the fields for sorting, then those fields would be copied into the sorting
field and concatenated. I could then run a single query with the Order By
statement on the sorting field and, in effect, sort with all 3 fields taken
into account. Then after the sorting has taken place I would need to erase
the concatenated fields from the sorting field.

I would be interested in any help or code. This is my first project with
Access, and although I do have some background in C++, I am learning the VB
and SQL as I go. Thanks...
 
Thank you for the very prompt response. Using GroupLevel.ControlSource seems
to be a great way to simplify the code for multiple field sorting. But
perhaps there are two reasons why I cannot use GroupLevel for sorting.

First, after looking at the suggested webpage and reading a little about
GroupLevel in my VB help, it looks like I can only use GroupLevel for
creating Reports. For this project I wanted the user to select the sorting
fields by using 3 option groups (the first option group for selecting the
first sorting field... etc.) on the form. Then, after the completed sort, I
wanted the user view the records resulting from the sort through fields on
the same form. The user would advance through the results of the sort
individually. I am not sure that I can do this with GroupLevel because it
seems usable for Reports but not for Forms.

Second, I tried to implement the code provided at the suggested site. When
I compiled the code I recieved the error message "Method or data member not
found" at the first occurrence of GroupLevel. This is the code I wrote:

Dim grpSort As Integer 'This value determines the sort
sequence
Dim varGroupLevel As Variant 'A variable for storing a created group
level

grpSort = (Optiongroup1 * 10) + Optiongroup2 'creating a unique
value to match with a unique sort sequence. Title = 1 and Author = 2 in both
option groups.

varGroupLevel = CreateGroupLevel("AfterSorting", "Title" And "Author",
-1, -1)

Select Case Forms!OptionForm!grpSort 'The name of my form is
OptionForm
Case 12 'Title then Author
Me.GroupLevel(0).ControlSource = "Title"
Me.GroupLevel(1).ControlSource = "Author"
Case 21 'Author then Title
Me.GroupLevel(0).ControlSource = "Author"
Me.GroupLevel(1).ControlSource = "Title"
End Select

Also, for what it's worth, when I bring up my tools/references.. I see the
following available referencees checked:
Visual Basic for Applications
Microsoft Access 10.0 Object Library
OLE Automation
Microsoft Forms 2.0 Object Library
Microsoft DAO 3.6 Object Library
Microsoft Active X Data Objects 2.7 Library
Microsoft Active X Data Objects Recordset 2.7 Library

I appreciate any further guidance or code. Thanks...
 
It would have saved a lot of time if you had provided your requirement about
being able to see the results in a form first.

You can set up your query with calculated fields/columns that provide the
sorting. Assuming your form and first option group names are:
Forms!frmRptSort!grpPrimarySort
there are four options with values 1-4 and labels of
1 Title
2 Author
3 Publisher
4 Publish Date
You can create a column in your query with an expression like:
PriSort: Choose(Forms!frmRptSort!grpPrimarySort, [Title], [Author],
[Publisher], Format([PubDate],"yyyymmdd"))
Do something similar for SecSort and TerSort
You can sort your query AND your report on these columns.
 
Back
Top