QRY good in query builder, bad as recordsource

[lamarw]

Hi, I have the following query:

SELECT [Active Publishers].[Full name], IIf([Domicile]=0,'Hall',[Active Publishers_1].[Last Name] & ' Home') AS FSGDomicile, FSGDomicile.DoWTime, [Active Publishers].PIF FROM ([Active Publishers] LEFT JOIN FSGDomicile ON [Active Publishers].[FS Group] = FSGDomicile.FSG) LEFT JOIN [Active Publishers] AS [Active Publishers_1] ON FSGDomicile.Domicile = [Active Publishers_1].PubID WHERE ((([Active Publishers].[FS Group])=1)) ORDER BY [Active Publishers].[Last Name], [Active Publishers].PIF, [Active Publishers].[First Name];

When I execute this in MSAccess 2007 query builder it works correctly. When I use it as a report recordsource it ignores PIF. In the builder it correctly selects the records and correctly sorts them as: Last name (always string), PIF (always numerical), First name (always string). When I use it as the recordsource to the report it results in Last name, First name. PIF is ignored. Any help you could provide would be very much appreciated. Lamar

[ssanfu]

Sorting in a query doesn't affect sorting in a report.

You have to do sorting in a report using the "Sorting and Grouping" command (Access 2000). I don't remember where it is located in A2007 - it is in a ribbon - maybe Design or Format group.

[lamarw]

That was it! Excellent! Thank you very much.

[ssanfu]

Ready to mark this solved?