ACCESS FEED: Re: [MS_AccessPros] Custom sort on report groups

Kamis, 22 Oktober 2015

Re: [MS_AccessPros] Custom sort on report groups

adding on to John's advice ...

put this equation in the RecordSource of the report so you can use Group & Sort

warm regards,
crystal

Bad Relationships? Make them good by enforcing referential integrity
video Tip: Enforce Referential Integrity on Access Relationships (cc)
http://www.youtube.com/watch?v=_zxxc9jzWEg

~ have an awesome day ~

On 10/22/2015 2:52 AM, John Viescas JohnV@msn.com [MS_Access_Professionals] wrote:

Are you trying to do a wildcard match? If so, then = is not the correct operator. Try this:

IIf([Venue2] LIKE "C H*", 1, IIf([Venue2] LIKE "G *", 6, IIf([Venue2] = "General", 12, 20)))

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Oct 22, 2015, at 7:42 AM, theaterkacy <no_reply@yahoogroups.com> wrote:

I would like to sort my report group in a custom order, rather than alphabetically.
The records appear under the correct headings, in alphabetical order, when I group by [Venue2], but the headings are alphabetical.

Ideally, this would sort based on a "sort" column from a table, but I get a mismatch error when I try to utilize this method. I do not think the relationships are set up appropriately between the tables and I don't want to have to rebuild everything to adjust this.

I have tried "simplifying" it by using

IIf([Venue2] = "C H*", 1, IIf([Venue2] = "G *", 6, IIf([Venue2] = "General", 12, 20)))

This put General first, and then all other records under C H, including the G and G*.

I have also tried to set the group on expression to:

=Switch([Venue2]="C H*",1,[Venue2]="G *",2,[Venue2]="General",3)

This only returns headers for C H and General; "G" records are listed, but are under the "C H" header. Other variants of C H*, such as C H L do not appear either.