UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Custom Group In Report, Access 2016    
 
   
lilus615
post Feb 16 2017, 11:37 PM
Post#1



Posts: 115
Joined: 6-September 06
From: CA, USA


I need to create a report and would like to group by category. I created a filter and it works in the query, but when I create the report and select group by that field. it does the A-Z order

below is my filter

IIf([Conversion Phase]="Pre-Close",1,IIf([Conversion Phase]="Close",2,IIf([Conversion Phase]="Pre-Conversion",3,IIf([Conversion Phase]="Conversion",4,IIf([Conversion Phase]="Post-Conversion",5)))))

thank you
Go to the top of the page
 
RJD
post Feb 17 2017, 12:10 AM
Post#2


UtterAccess VIP
Posts: 7,281
Joined: 25-October 10
From: Gulf South USA


Hi: I'm a bit confused by your post. You say the IIf statement is a filter, but I would have expected it to be a new query field instead, not a filter. And I would have used a Switch function in the query instead (it's just easier to follow IMHO) ...

In Design View ...

MySortField: Switch([Conversion Phase]="Pre-Close",1,[Conversion Phase]="Close",2,[Conversion Phase]="Pre-Conversion",3,[Conversion Phase]="Conversion",4,[Conversion Phase]="Post-Conversion",5,True,9)

Or, in SQL View ...

Switch([Conversion Phase]="Pre-Close",1,[Conversion Phase]="Close",2,[Conversion Phase]="Pre-Conversion",3,[Conversion Phase]="Conversion",4,[Conversion Phase]="Post-Conversion",5,True,9) As MySortField

(The last part is a "catch-all", in case something else is found)

(Name the alias whatever you wish)

Then in the report, using the query as the record source, group by MySortField, but display the [Conversion Phase] in the group header.

If this is not what you are attempting, perhaps you could post a small example db with table, query and report for us to use to show you how this is done.

(Oh, and it is a good idea not to have spaces in your object names. Just a thought ...)

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
lilus615
post Feb 17 2017, 12:30 AM
Post#3



Posts: 115
Joined: 6-September 06
From: CA, USA


that worked!!! Thank you very much
Go to the top of the page
 
RJD
post Feb 17 2017, 12:45 AM
Post#4


UtterAccess VIP
Posts: 7,281
Joined: 25-October 10
From: Gulf South USA


You are very welcome. My pleasure. Glad that worked for you. thumbup.gif

Good luck with your project.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    26th March 2017 - 11:43 PM