NotSoRandomOne
Registered User.
- Local time
- Today, 16:07
- Joined
- Sep 15, 2009
- Messages
- 51
[Solved it. See end.]
I am putting together an information DB for keeping track of notes from books and periodicals. It consists of a 'SourcesTable,' a 'PeriodicalsTable,' and an 'AuthorsTable.' I am trying to get the sort for a combobox to correctly alphabetize.
I created a 'SourcesQuery' to build the strings into a reference. To make it easier, I broke it into several fields in order not to go insane following the logic. Examples are:
Field1: ID
Field2: TheAuthor: IIf(IsNull([SourcesTable.Author]),"",[AuthorsTable.Author]+", ")
Field3: ThePeriodical: IIf([PeriodicalsTable.Periodical],[PeriodicalsTable.Periodical] & ", ","")
...
As you can see, I'm just adding the commas where appropriate. Then, the full title can be obtained in a field by
TheTitle: IIf([TheAuthor],[TheAuthor],"")+[Title]+", "
The problem is doing it this way, I can't figure out how to sort it based purely on the final list text.
If I don't do anything other than the previous, switching to data view from design view prompts me for 'TheAuthor'. I can eliminate that problem if I don't specify the final field as sorted. If I add another field that just concatenates the items directly together in the database, via
MySortField: AuthorDB.Author & PeriodicalDB.Periodical & SourcesDB.Title
I can get MySortField to sort, but that sort sorts FIRST by Author, THEN by periodical, THEN by title. I want it to sort directly from the text in the field, not by the subfields.
I know I can create a form and an auxiliary table, put a button on it with some VB code to fill that temp table with the text, and then have it sort correctly, but is there something I'm overlooking with the existing work I've done before I attack it another way?
Thanks,
David
I forgot to say that there isn't a guarantee there is any text for a periodical name, nor is there even a guarantee that there will be an author linked to a certain piece of information.
[After posting, I tried "Expr1: [AuthorsTable].[Author] & [Title]" in a field by itself, and this time it sorted it the way I wanted. Don't know why it didn't work before!]
I am putting together an information DB for keeping track of notes from books and periodicals. It consists of a 'SourcesTable,' a 'PeriodicalsTable,' and an 'AuthorsTable.' I am trying to get the sort for a combobox to correctly alphabetize.
I created a 'SourcesQuery' to build the strings into a reference. To make it easier, I broke it into several fields in order not to go insane following the logic. Examples are:
Field1: ID
Field2: TheAuthor: IIf(IsNull([SourcesTable.Author]),"",[AuthorsTable.Author]+", ")
Field3: ThePeriodical: IIf([PeriodicalsTable.Periodical],[PeriodicalsTable.Periodical] & ", ","")
...
As you can see, I'm just adding the commas where appropriate. Then, the full title can be obtained in a field by
TheTitle: IIf([TheAuthor],[TheAuthor],"")+[Title]+", "
The problem is doing it this way, I can't figure out how to sort it based purely on the final list text.
If I don't do anything other than the previous, switching to data view from design view prompts me for 'TheAuthor'. I can eliminate that problem if I don't specify the final field as sorted. If I add another field that just concatenates the items directly together in the database, via
MySortField: AuthorDB.Author & PeriodicalDB.Periodical & SourcesDB.Title
I can get MySortField to sort, but that sort sorts FIRST by Author, THEN by periodical, THEN by title. I want it to sort directly from the text in the field, not by the subfields.
I know I can create a form and an auxiliary table, put a button on it with some VB code to fill that temp table with the text, and then have it sort correctly, but is there something I'm overlooking with the existing work I've done before I attack it another way?
Thanks,
David
I forgot to say that there isn't a guarantee there is any text for a periodical name, nor is there even a guarantee that there will be an author linked to a certain piece of information.
[After posting, I tried "Expr1: [AuthorsTable].[Author] & [Title]" in a field by itself, and this time it sorted it the way I wanted. Don't know why it didn't work before!]
Last edited:
