custom sort in report grouping

  • Thread starter Thread starter icccapital
  • Start date Start date
I

icccapital

I have many groups on a report page and there is one group that I would like
to have sorted with a custom sort because there are only a few possible
values that I would like in a certain order on the report. All I can find
int eh sorting and grouping is sorting A-Z or Z-A. Is there a way to do a
custom sort in vba?

I am working in Access 2007
 
Rather than VBA, there are usually calculated fields which you can put into
a query and sort by those. You can even use expressions in the
Sorting/Grouping box instead of field names. If the sort order is more
random then you may even need to consider adding another field to your
underlying table.
What is this custom sort which you want?
Evi
 
Well, I have headers for Security Types (Cash, Equity, ADR, Fixed Income)

I would like it to sort in this order, but obviously if I sort this it will
sort (ADR, Cash ....).

so is your suggestion to have another field in the table that would
basically map Cash to 1, Equity to 2, ADR to 3, Fixed Income to 4, then add
that to my report in the same section making it not visible, and then sort on
that? Sounds like it might work.
 
Calculated field:
SortField: Switch([Type] = "Cash", 1, [Type] = "Equity", 2, [Type] = "ADR",
3, [Type] = "Fixed Income",4)

Add the above to your query. It has to be visible so the report can see it.
Use SortField as the Group Field in the report.
 
That's what I do when I need to sort something in a fairly arbitrary order.
It also gives me a simple way of changing the order too, at a later date.
Evi
 
That worked great, thanks. I haddn't seen the switch function before.

Klatuu said:
Calculated field:
SortField: Switch([Type] = "Cash", 1, [Type] = "Equity", 2, [Type] = "ADR",
3, [Type] = "Fixed Income",4)

Add the above to your query. It has to be visible so the report can see it.
Use SortField as the Group Field in the report.

--
Dave Hargis, Microsoft Access MVP


icccapital said:
Well, I have headers for Security Types (Cash, Equity, ADR, Fixed Income)

I would like it to sort in this order, but obviously if I sort this it will
sort (ADR, Cash ....).

so is your suggestion to have another field in the table that would
basically map Cash to 1, Equity to 2, ADR to 3, Fixed Income to 4, then add
that to my report in the same section making it not visible, and then sort on
that? Sounds like it might work.
Click to expand...
Click to expand...
 
Hi Dave,
I just came across this message and thought that I would tell you that it helped me as well.

Ivan



Klatu wrote:

Re: custom sort in report grouping
07-Mar-08

Calculated field
SortField: Switch([Type] = "Cash", 1, [Type] = "Equity", 2, [Type] = "ADR",
3, [Type] = "Fixed Income",4

Add the above to your query. It has to be visible so the report can see it
Use SortField as the Group Field in the report

--
Dave Hargis, Microsoft Access MV

:

EggHeadCafe - Software Developer Portal of Choice
WPF And The Model View View Model Pattern
http://www.eggheadcafe.com/tutorial...b-7374d3da3425/wpf-and-the-model-view-vi.aspx
 
I cnat get this to work in my database. I keep receiving errors.



Ivan Jankovic wrote:

Thanks
25-Sep-09

Hi Dave,
I just came across this message and thought that I would tell you that it helped me as well.

Ivan



Klatu wrote:

Re: custom sort in report grouping
07-Mar-08

Calculated field:
SortField: Switch([Type] = "Cash", 1, [Type] = "Equity", 2, [Type] = "ADR",
3, [Type] = "Fixed Income",4)

Add the above to your query. It has to be visible so the report can see it.
Use SortField as the Group Field in the report.

--
Dave Hargis, Microsoft Access MVP


:

EggHeadCafe - Software Developer Portal of Choice
WPF And The Model View View Model Pattern
http://www.eggheadcafe.com/tutorial...b-7374d3da3425/wpf-and-the-model-view-vi.aspx

Previous Posts In This Thread:

custom sort in report grouping
I have many groups on a report page and there is one group that I would like
to have sorted with a custom sort because there are only a few possible
values that I would like in a certain order on the report. All I can find
int eh sorting and grouping is sorting A-Z or Z-A. Is there a way to do a
custom sort in vba?

I am working in Access 2007

Rather than VBA, there are usually calculated fields which you can put intoa
Rather than VBA, there are usually calculated fields which you can put into
a query and sort by those. You can even use expressions in the
Sorting/Grouping box instead of field names. If the sort order is more
random then you may even need to consider adding another field to your
underlying table.
What is this custom sort which you want?
Evi

like

Well, I have headers for Security Types (Cash, Equity, ADR, Fixed Income)I
Well, I have headers for Security Types (Cash, Equity, ADR, Fixed Income)

I would like it to sort in this order, but obviously if I sort this it will
sort (ADR, Cash ....).

so is your suggestion to have another field in the table that would
basically map Cash to 1, Equity to 2, ADR to 3, Fixed Income to 4, then add
that to my report in the same section making it not visible, and then sort on
that? Sounds like it might work.

:

Re: custom sort in report grouping
Calculated field:
SortField: Switch([Type] = "Cash", 1, [Type] = "Equity", 2, [Type] = "ADR",
3, [Type] = "Fixed Income",4)

Add the above to your query. It has to be visible so the report can see it.
Use SortField as the Group Field in the report.

--
Dave Hargis, Microsoft Access MVP


:

That's what I do when I need to sort something in a fairly arbitrary order.
That's what I do when I need to sort something in a fairly arbitrary order.
It also gives me a simple way of changing the order too, at a later date.
Evi

will
add
on
into
would
possible
find
do a

That worked great, thanks. I haddn't seen the switch function before.
That worked great, thanks. I haddn't seen the switch function before.

:

Thanks
Hi Dave,
I just came across this message and thought that I would tell you that it helped me as well.

Ivan



Klatu wrote:

Re: custom sort in report grouping
07-Mar-08

Calculated field:
SortField: Switch([Type] = "Cash", 1, [Type] = "Equity", 2, [Type] = "ADR",
3, [Type] = "Fixed Income",4)

Add the above to your query. It has to be visible so the report can see it.
Use SortField as the Group Field in the report.

--
Dave Hargis, Microsoft Access MVP


:

EggHeadCafe - Software Developer Portal of Choice
WPF And The Model View View Model Pattern
http://www.eggheadcafe.com/tutorial...b-7374d3da3425/wpf-and-the-model-view-vi.aspx


Submitted via EggHeadCafe - Software Developer Portal of Choice
More Fun with Fluent NHibernate Automapping
http://www.eggheadcafe.com/tutorial...9-81ee42171b00/more-fun-with-fluent-nhib.aspx
 
Maggie said:
I cnat get this to work in my database. I keep receiving errors.
Click to expand...


Gee, Maggie, could you tell us what you did and what errors
you got?

BTW, I strongly prefer having the sort order field in the
table rather than in a query expression.
 
We can't see your database. What expression did you try and where?


--
Duane Hookom
MS Access MVP


I cnat get this to work in my database. I keep receiving errors.



Ivan Jankovic wrote:

Thanks
25-Sep-09

Hi Dave,
I just came across this message and thought that I would tell you that it
helped me as well.

Ivan



Klatu wrote:

Re: custom sort in report grouping
07-Mar-08

Calculated field:
SortField: Switch([Type] = "Cash", 1, [Type] = "Equity", 2, [Type] =
"ADR",
3, [Type] = "Fixed Income",4)

Add the above to your query. It has to be visible so the report can see
it.
Use SortField as the Group Field in the report.

--
Dave Hargis, Microsoft Access MVP


:

EggHeadCafe - Software Developer Portal of Choice
WPF And The Model View View Model Pattern
http://www.eggheadcafe.com/tutorial...b-7374d3da3425/wpf-and-the-model-view-vi.aspx

Previous Posts In This Thread:

custom sort in report grouping
I have many groups on a report page and there is one group that I would
like
to have sorted with a custom sort because there are only a few possible
values that I would like in a certain order on the report. All I can find
int eh sorting and grouping is sorting A-Z or Z-A. Is there a way to do a
custom sort in vba?

I am working in Access 2007

Rather than VBA, there are usually calculated fields which you can put
intoa
Rather than VBA, there are usually calculated fields which you can put
into
a query and sort by those. You can even use expressions in the
Sorting/Grouping box instead of field names. If the sort order is more
random then you may even need to consider adding another field to your
underlying table.
What is this custom sort which you want?
Evi

like

Well, I have headers for Security Types (Cash, Equity, ADR, Fixed Income)I
Well, I have headers for Security Types (Cash, Equity, ADR, Fixed Income)

I would like it to sort in this order, but obviously if I sort this it
will
sort (ADR, Cash ....).

so is your suggestion to have another field in the table that would
basically map Cash to 1, Equity to 2, ADR to 3, Fixed Income to 4, then
add
that to my report in the same section making it not visible, and then sort
on
that? Sounds like it might work.

:

Re: custom sort in report grouping
Calculated field:
SortField: Switch([Type] = "Cash", 1, [Type] = "Equity", 2, [Type] =
"ADR",
3, [Type] = "Fixed Income",4)

Add the above to your query. It has to be visible so the report can see
it.
Use SortField as the Group Field in the report.

--
Dave Hargis, Microsoft Access MVP


:

That's what I do when I need to sort something in a fairly arbitrary
order.
That's what I do when I need to sort something in a fairly arbitrary
order.
It also gives me a simple way of changing the order too, at a later date.
Evi

will
add
on
into
would
possible
find
do a

That worked great, thanks. I haddn't seen the switch function before.
That worked great, thanks. I haddn't seen the switch function before.

:

Thanks
Hi Dave,
I just came across this message and thought that I would tell you that it
helped me as well.

Ivan



Klatu wrote:

Re: custom sort in report grouping
07-Mar-08

Calculated field:
SortField: Switch([Type] = "Cash", 1, [Type] = "Equity", 2, [Type] =
"ADR",
3, [Type] = "Fixed Income",4)

Add the above to your query. It has to be visible so the report can see
it.
Use SortField as the Group Field in the report.

--
Dave Hargis, Microsoft Access MVP


:

EggHeadCafe - Software Developer Portal of Choice
WPF And The Model View View Model Pattern
http://www.eggheadcafe.com/tutorial...b-7374d3da3425/wpf-and-the-model-view-vi.aspx


Submitted via EggHeadCafe - Software Developer Portal of Choice
More Fun with Fluent NHibernate Automapping
http://www.eggheadcafe.com/tutorial...9-81ee42171b00/more-fun-with-fluent-nhib.aspx
Click to expand...
 
Hello, I'm having a problem along the same lines and I was wondering if you all could assist me.

I would like to create a report where the user can enter the sort order via a Form. I'm a novice here. I have eight topic areas, I wanted to allow the user to enter the order, so that the report generates in that order.

Topic A = 4
Topic B = 2
Topic C = 1
Topic D= 7
Topic E = 8

Of course, the sort order of the report would be 1, 2, 3, 4, ...8. Then the user can change the numbering so that they can change the order of the report.

I see instructions for using sortorder: switch and iif, in the query but I want the user to be able to change the number of the sort.

Thanks for your help.
 
Back
Top