Sort a report by different fields... (1 Viewer)

[doran_doran]

SELECT tbl_groups.GA_Number, tbl_groups.PlanNum, tbl_groups.TracID, tbl_groups.SystemType, tbl_groups.PYE, tbl_groups.CurrentStatus, tbl_groups.SvcType, tbl_groups.Market_sgmt, DLookUp("[EmployerType]","tblPlanMatrix","[Ga_Record_ID_2] = '" & [Ga_Record_ID_2] & "'") AS ER, tbl_groups.Participant_Number, tbl_groups.Asset_PYB, tbl_groups.[1stYearofSvc], tbl_groups.Plan_Name, tbl_groups.Primary_Administrator, tbl_groups.Team_Leader, tbl_groups.Manager, DLookUp("[Relationship_Manager]","tblPlanMatrix","[Ga_Record_ID_2] = '" & [Ga_Record_ID_2] & "'") AS RM, tbl_groups.Region, DLookUp("[DistrictManager]","tblPlanMatrix","[Ga_Record_ID_2] = '" & [Ga_Record_ID_2] & "'") AS DM, DLookUp("[Financial_Advisor]","tblPlanMatrix","[Ga_Record_ID_2] = '" & [Ga_Record_ID_2] & "'") AS FA
FROM tbl_groups;

Ok, above is my query. i wanna build a report based on the above query. However, i want to be able to click on any of the header (ga number, or plan name or any field header) and sort it, either asending or decending.

How do I do that? I have seen it before but dont remember how to do this. Also, I would like a subtile to be dynamic like it will "Master Plan List - Sorted by Field Name". That way end user will know how the report is sorted.

Thanks in advance. I am not too hopefull, since this is not a easy task. But any help will be appreciated.

Regards
Dianna

 

[Matthew Snook]

Let's imagine you have a button on your main form called "cmd_Sort_GA_Number" and a subform on the same main form which is called "Child_Form." The click event of the button could look somewhat like this:
'********************************
Private Sub cmd_Sort_GA_Number_Click()

'establish which field to sort......
dim txt_sort as text
txt_sort = "tbl_groups.GA_Number"

'Write this field into the orderby section of your query.......
Me.Child_Form.Form.RecordSource = "SELECT tbl_groups.GA_Number, tbl_groups.PlanNum, tbl_groups.TracID, tbl_groups.SystemType, tbl_groups.PYE, tbl_groups.CurrentStatus, tbl_groups.SvcType, tbl_groups.Market_sgmt, DLookUp("[EmployerType]","tblPlanMatrix","[Ga_Record_ID_2] = '" & [Ga_Record_ID_2] & "'") AS ER, tbl_groups.Participant_Number, tbl_groups.Asset_PYB, tbl_groups.[1stYearofSvc], tbl_groups.Plan_Name, tbl_groups.Primary_Administrator, tbl_groups.Team_Leader, tbl_groups.Manager, DLookUp("[Relationship_Manager]","tblPlanMatrix","[Ga_Record_ID_2] = '" & [Ga_Record_ID_2] & "'") AS RM, tbl_groups.Region, DLookUp("[DistrictManager]","tblPlanMatrix","[Ga_Record_ID_2] = '" & [Ga_Record_ID_2] & "'") AS DM, DLookUp("[Financial_Advisor]","tblPlanMatrix","[Ga_Record_ID_2] = '" & [Ga_Record_ID_2] & "'") AS FA
FROM tbl_groups Order by " & txt_sort & ";"

'Requery the subform.....
Me.Child_Form.Requery
End Sub
'*****************************

The same button could change the text or title of your form.

Matt

 

[doran_doran]

But...

Nice job, that's what i need i guess. But I need this to be for report. and of course, b/4 end user go to the report, they have to click a button on a form. and i am assuming thats where i will put this.

But is there a way, to put all the fields in a combo and select the field and run the same report sorted depending on what field was picked.

Thanks a bunch for helping me.

Dianna

 

[doran_doran]

How to Sort a Report from a Pop-Up Form

I got it from Microsoft's website, it even include a sample database. Thanks anyway for your help.

http://support.microsoft.com/default.aspx?scid=kb;en-us;146310


sample db is here:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;175072