Order By Ignored

[gray]

Hi

WinXPPro SP2
Access2002/2007

I'm building a recordsource on-the-fly for a form which I want to appear in a particular order....

Before I throw myself under the next passing train can anyone tell me why the ORDER BY on this simple Select is ignored please?

I've tried ASC and Desc .. I've run it in the QBE and it selects the correct records but refuses to sort them.. there are no errors or failures

SELECT 
TBL2.Unique_No,
TBL2.Source_Table_Name,
TBL2.Source_Field_Name, 
TBL2.Table_Friendly_Name,
TBL1.* 
FROM ((ADDRS AS TBL1) 
INNER JOIN Captions AS TBL2
ON TBL1.Parent_Table_Unique_No=TBL2.Unique_No)
ORDER BY TBL2.Table_Friendly_Name

TBL2.Table_Friendly_Name = a Text Field (not Memo) ...and the column name is not in TBL1

Derrrr...?

 

[jdraw]

I set up 2 tables to test your code and it works fine for me.

Table Captions:
table_name field_name data_type length
Captions UNique_No Long 4
Captions Source_Table_Name Text 255
Captions Source_Field_name Text 255
Captions Table_friendly_Name Text 255

Data attached as Captions.jpg

Table : MyAddrs

table_name field_name data_type length
MyAddrs ID Long 4
MyAddrs Parent_Table_Unique_no Long 4
MyAddrs PTOtherField1 Text 50
MyAddrs PTOtherfield2 Text 50

Data attached as MyAddrs.jpg

Query:

SELECT
TBL2.Unique_No,
TBL2.Source_Table_Name,
TBL2.Source_Field_Name,
TBL2.Table_Friendly_Name,
TBL1.*
FROM ((MyADDRS AS TBL1)
INNER JOIN Captions AS TBL2
ON TBL1.Parent_Table_Unique_No=TBL2.Unique_No)
ORDER BY TBL2.Table_Friendly_Name

Query result attached as Query41.jpg

 

[gray]

Thanks Jdraw... So its not my Select ... all very odd... when in doubt re-boot!! thanks

 

[gray]

Well, well ... re-boot, compact and repair.... type into a new query and it works!