Query good in query builder, bad as report recordsource 1

[lamarw]

Hi,
Well, I can't find anything already posted. Can you direct me to the answer to the following?

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];

The preceding query works correctly in the query builder but when it is used as the recordsource for a report it is not correctly ORDERing the records. The sort ignores PIF which is numerical (0, 1, or 10). Last name ASC, then PIF ASC, then First name ASC is the desired action. However the end result is Last name ASC, then First name ASC. The PIF is ignored. Does anybody have any idea why?

Lamar

 

[MajP]

This is because you have some sorting and grouping which overrides the query sort order. Check your sorting and grouping.

 

[Andrzejek]

Your [First Name] is not part of a Select part, you may want to add it to the end.

Also, you may want to try:
ORDER BY 1, 4, 5
If your last field will be [First Name] as 5th field

Just a guess...

Have fun.

---- Andy

 

[dhookom]

I agree with MajP. Don't waste your time sorting a report's record source. Use the sorting in the report design. That's where it needs to be.

Duane
Hook'D on Access
MS Access MVP

 

[lamarw]

MajP, Andrzejek, dhookom thank you for your responses. MajP that was it! Excellent! Thank you so much.

 

[dhookom]

MajP got it right and deserves a star.

Duane
Hook'D on Access
MS Access MVP