Sort not working- ARRRGGGHH

[simon_marklar]

Hi guys,


I got a weird problem. trying to bring back data from my database, i have a query that has worked for the last year perfectly. today, its not. Im asking it to sort via several fields, first is the state, second is the suburb. so what i should (and have) ended up with is a list of states and suburbs in alphabetical order, grouped by state. all of a sudden, W comes before G which comes before A. wtf?

the SQL is quite big as it joins several tables, but the order clause is this:
"ORDER BY States.Description, public_publications.mLocation, public_publications.title, PublishingDays.OrderLevel, [ABC-Circulation].iYear DESC , [ABC-Circulation].AuditPeriod DESC;"

States.Description = Name of state
public_publications.mLocation = suburb
public_publications.title = title of publication
PublishingDays.OrderLevel = special order level (applys only when one publication has more than one entry)
year and auditperiod are the year and the period of the year that the audit takes.

even if i take out all the sorting and leave in "public_publications.mLocation" it still thinks that G comes first, then W, then B, then F etc etc. ARGGGHHGH

is there anything else that effects sorting? this is driving me CRAZY!!!!!!!!

i have compacted and repaired. I have copied the SQL, re-wrote it from scratch, removed all ordering, nothing works.

again i will say that this worked 100% right 3 months ago (last time i used the query) - and i have not changed the query (at least i dont remember doing it, and i have not had a reason to change it). hence why im going insane.

SELECT 
	PublicationTypes.tPublicationType, 
	[ABC-PublicationSubTypes].tDescription, 
	public_publications.title, 
	IIf([Exclusions],'*' & [Issues],[Issues]) AS NumOfIssues, 
	IIf([ABC-Circulation].[DayPublished]=20,[SpecialPublishingDay],[PublishingDays].[DayPublished]) AS Days_Published, 
	[ABC-Circulation].CoverPrice, 
	IIf(IsNull([ABC-Circulation].[CoverPriceDay]),[PublishingDays].[DayPublished],IIf([ABC-Circulation].[CoverPriceDay]=20,[SpecialPublishingDay],PublishingDays_1.DayPublished)) AS CoverPriceDay, 
	[ABC-Circulation].CoverPrice2, 
	IIf([ABC-Circulation].[CoverPrice2Day]=20,[SpecialPublishingDay],PublishingDays_2.DayPublished) AS CoverPrice2Day, 
	[ABC-Circulation].CoverPrice3, 
	IIf([ABC-Circulation].[CoverPrice3Day]=20,[SpecialPublishingDay],PublishingDays_3.DayPublished) AS CoverPrice3Day, 
	AuditTypesMaster.AuditType, Format([dStartDate],"mmm") & " to " & Format([dEndDate],"mmm") & " " & [ABC-Circulation].[iYear] AS Period, 
	IIf([InExcess],[Australia] & "+",[Australia]) AS ANPS, 
	[ABC-Circulation].inclAccomAirlineSales, 
	[ABC-Circulation].inclEducationalSales, 
	[ABC-Circulation].inclEventSales, 
	[ABC-Circulation].inclMultiplePublicationSales, 
	IIf([NZInExcess],[NewZealand] & "+",[NewZealand]) AS NewZealandFigures, 
	IIf([OCInExcess],[OtherCountries] & "+",[OtherCountries]) AS OCFigures, 
	IIf([exclInExcess],[exclAustraliaOther] & "+",[exclAustraliaOther]) AS exclAUSFigures, 
	IIf([Exclusions],[ExcludedDates],Null) AS Remarks1, 
	[ABC-Circulation].BumperIssues, 
	[ABC-Circulation].BumperIssueDates, 
	PublishingDays.OrderLevel, 
	public_publications.mLocation, 
	public_publications.CompanyID, 
	States.Description, 
	PublishingDays.OrderLevel, 
	[ABC-Circulation].iYear, 
	[ABC-Circulation].AuditPeriod
	FROM ((((AuditTypesMaster 
		INNER JOIN ((((([ABC-Circulation] 
			INNER JOIN public_publications ON [ABC-Circulation].PubID = public_publications.Pubid) 
				LEFT JOIN PublishingDays ON [ABC-Circulation].DayPublished = PublishingDays.lDayIndex) 
				LEFT JOIN PublishingDays AS PublishingDays_1 ON [ABC-Circulation].CoverPriceDay = PublishingDays_1.lDayIndex) 
				LEFT JOIN PublishingDays AS PublishingDays_2 ON [ABC-Circulation].CoverPrice2Day = PublishingDays_2.lDayIndex) 
				LEFT JOIN PublishingDays AS PublishingDays_3 ON [ABC-Circulation].CoverPrice3Day = PublishingDays_3.lDayIndex) 
			ON AuditTypesMaster.iIndex = [ABC-Circulation].lAuditType) 
		LEFT JOIN PublicationTypes ON public_publications.lPublicationType = PublicationTypes.lIndex) 
		LEFT JOIN [ABC-PublicationSubTypes] ON public_publications.lPublicationSubType = [ABC-PublicationSubTypes].lSubTypeIndex) 
		LEFT JOIN [ABC-AuditPeriods] ON [ABC-Circulation].AuditPeriod = [ABC-AuditPeriods].lAuditPeriodID) 
		INNER JOIN States 
			ON public_publications.state = States.StateID
	WHERE (((Format([dStartDate],"mmm") & " to " & Format([dEndDate],"mmm") & " " & [ABC-Circulation].[iYear])="Jan to Jun 2007" 
		Or (Format([dStartDate],"mmm") & " to " & Format([dEndDate],"mmm") & " " & [ABC-Circulation].[iYear])="Jul to Dec 2006" 
		Or (Format([dStartDate],"mmm") & " to " & Format([dEndDate],"mmm") & " " & [ABC-Circulation].[iYear])="Apr to Jun 2007" 
		Or (Format([dStartDate],"mmm") & " to " & Format([dEndDate],"mmm") & " " & [ABC-Circulation].[iYear])="Jan to Mar 2007" 
		Or (Format([dStartDate],"mmm") & " to " & Format([dEndDate],"mmm") & " " & [ABC-Circulation].[iYear])="Oct to Dec 2006") 
		AND ((public_publications.lPublicationType)=5))
	ORDER BY 
		public_publications.lPublicationType, 
		States.Description, 
		public_publications.mLocation, 
		public_publications.title, 
		public_publications.CompanyID, 
		PublishingDays.OrderLevel, 
		[ABC-Circulation].iYear DESC , 
		[ABC-Circulation].AuditPeriod DESC;

 

[Guus2005]

A report or a bound form could change the order of your query.
That's why i don't sort my queries. It's a waste of computer time when they are sorted in a different (or the same) order afterwards.

Try removing the sort order and check the sort order of the object in which you show the query.

HTH

 

[simon_marklar]

A report or a bound form could change the order of your query.
That's why i don't sort my queries. It's a waste of computer time when they are sorted in a different (or the same) order afterwards.

Try removing the sort order and check the sort order of the object in which you show the query.

HTH

sorry, i should of mentioned that there's nothing bound to this query; it exists solely to export data out to ms excel for presentation. The data is read off the exported .xls file into an array and then printed to the specified format. I cannot sort it in excel as i need to sort it based on 5 fields and excel only gives me 3. I might be able to do it in VBA (which is what im looking at now) but i see no reason why sorting on every field except mlocation works as it should. I have removed all sorting, then asked to sort by title (perfect) state(perfect) amount (perfect) location (doesnt bloody work!)

If i sort by name, i can click on the column heading in the data view and sort, but if i sort by location, the sort options are greyed out but only if i right click on the location field. every other field will let me sort

somethign weird is going on.

 

[Banana]

A report or a bound form could change the order of your query.
That's why i don't sort my queries. It's a waste of computer time when they are sorted in a different (or the same) order afterwards.

Erm, I beg to differ.

I *wouldn't* depend on sorting executed by clickion on that A...Z button because you have no guarantee that it will carry over, and because you cannot sort more than one column. Finally, sorting in a query is much faster than sorting from user.

Now to OP's question...

If you had something that was working, then just suddenly stopped and you didn't change anything, even the tiniest bit, I would suspect database corruption.

Create a new blank database. Important! Make sure you have Name AutoCorrect off (Tools -> Options; in the pane, click General (I think?) and turn off three checkbox within the frame "Name AutoCorrect". Don't confuse "Name AutoCorrect" with "AutoCorrect". Source

When you've created the blank database, go to File -> Get External Data -> Import.

In dialog, select "All objects" then "Select All" from your old database.

This will bring everything over to the new file. Now run it and see if it works.



In fact, I usually don't expose the button to my users unless they *actually* need that functionality.

 

[simon_marklar]

If you had something that was working, then just suddenly stopped and you didn't change anything, even the tiniest bit, I would suspect database corruption.

Create a new blank database. Important! Make sure you have Name AutoCorrect off (Tools -> Options; in the pane, click General (I think?) and turn off three checkbox within the frame "Name AutoCorrect". Don't confuse "Name AutoCorrect" with "AutoCorrect". Source

When you've created the blank database, go to File -> Get External Data -> Import.

In dialog, select "All objects" then "Select All" from your old database.

This will bring everything over to the new file. Now run it and see if it works.

that fixed it!!!!!!

*does dance of joy*

thank you sooooo much!

 

[Banana]

Glad it solved the problem!