Sort Not Working- ARRRGGGHH

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.


:mad:

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;