Special syntax for ORDER BY ?????

[Bill Stanton]

In the following UNION query the "ORDER BY [DonorName]" you
see in the last line is not taking affect. I had to add
Me.Orderby = "DonorName" in the using form's open code to
get the records to sort. Is there a special syntax that needs to be
used when the field name is defined within SQL?

I tried the statement with and without brackets.

Thanks,
Bill

SELECT ALL
[Families].[FamilyName]AS DonorName,[DonRegFam].[FundID],
[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM ([DonRegFam] INNER JOIN
[Funds] ON [DonRegFam].[FundID] = [Funds].[FundID])
INNER JOIN [Families]
ON [DonRegFam].[FamilyID] = [Families].[FamilyID]

UNION ALL SELECT
[Registry].[LastName] & "," & [Registry].[FirstName] AS DonorName,
[DonRegInd].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM ([DonRegInd] INNER JOIN [Funds]
ON [DonRegInd].[FundID] = [Funds].[FundID])
INNER JOIN [Registry]
ON [DonRegInd].[RegistryID] = [Registry].[RegistryID]
ORDER BY [DonorName];

 

[Jason]

You might try using the full syntax, i.e. [Registry].
[FirstName] instead of your assigned name. I've found I
had to do this in some instances (not sure why??)

Jason

-----Original Message-----
In the following UNION query the "ORDER BY [DonorName]" you
see in the last line is not taking affect. I had to add
Me.Orderby = "DonorName" in the using form's open code to
get the records to sort. Is there a special syntax that needs to be
used when the field name is defined within SQL?

I tried the statement with and without brackets.

Thanks,
Bill

SELECT ALL
[Families].[FamilyName]AS DonorName,[DonRegFam].[FundID],
[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM ([DonRegFam] INNER JOIN
[Funds] ON [DonRegFam].[FundID] = [Funds].[FundID])
INNER JOIN [Families]
ON [DonRegFam].[FamilyID] = [Families].[FamilyID]

UNION ALL SELECT
[Registry].[LastName] & "," & [Registry].[FirstName] AS DonorName,

[DonRegInd].[FundID],[Funds].[FundTitle],[DOE],[Amount],

[Type]
FROM ([DonRegInd] INNER JOIN [Funds]
ON [DonRegInd].[FundID] = [Funds].[FundID])
INNER JOIN [Registry]
ON [DonRegInd].[RegistryID] = [Registry].[RegistryID]
ORDER BY [DonorName];


.

 

[John Spencer (MVP)]

Try the following UNDOCUMENTED and probably UNSUPPORTED Order by clause.

ORDER BY 1

You just specify the column number.

 

[Van T. Dinh]

[DonorName] is the Name of a Calculated Field so you cannot use it in the
Query itself.

Ken's suggestion should work but I prefer the full syntax of

.... ORDER BY [Families].[FamilyName]

 

[Michel Walsh]

Hi,

Jet does not allow alias in the ORDER BY segment, you have to use the initial field name,
or the expression represented by the alias.



Vanderghast, Access MVP

You might try using the full syntax, i.e. [Registry].
[FirstName] instead of your assigned name. I've found I
had to do this in some instances (not sure why??)

Jason

-----Original Message-----
In the following UNION query the "ORDER BY [DonorName]" you
see in the last line is not taking affect. I had to add
Me.Orderby = "DonorName" in the using form's open code to
get the records to sort. Is there a special syntax that needs to be
used when the field name is defined within SQL?

I tried the statement with and without brackets.

Thanks,
Bill

SELECT ALL
[Families].[FamilyName]AS DonorName,[DonRegFam].[FundID],
[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM ([DonRegFam] INNER JOIN
[Funds] ON [DonRegFam].[FundID] = [Funds].[FundID])
INNER JOIN [Families]
ON [DonRegFam].[FamilyID] = [Families].[FamilyID]

UNION ALL SELECT
[Registry].[LastName] & "," & [Registry].[FirstName] AS DonorName,

[DonRegInd].[FundID],[Funds].[FundTitle],[DOE],[Amount],

[Type]
FROM ([DonRegInd] INNER JOIN [Funds]
ON [DonRegInd].[FundID] = [Funds].[FundID])
INNER JOIN [Registry]
ON [DonRegInd].[RegistryID] = [Registry].[RegistryID]
ORDER BY [DonorName];


.

 

[Michel Walsh]

Hi,


It was documented, and I think it is part of the Standard SQL, but "deprecated" (I think
this is the expression for something that is recommended to NOT use, since it depends on the order
of field, as mentioned, in the SELECT clause, so, error prone (adding fields, forgetting to modify
the ORDER BY clause)).



Vanderghast, Access MVP

Try the following UNDOCUMENTED and probably UNSUPPORTED Order by clause.

ORDER BY 1

You just specify the column number.

In the following UNION query the "ORDER BY [DonorName]" you
see in the last line is not taking affect. I had to add
Me.Orderby = "DonorName" in the using form's open code to
get the records to sort. Is there a special syntax that needs to be
used when the field name is defined within SQL?

I tried the statement with and without brackets.

Thanks,
Bill

SELECT ALL
[Families].[FamilyName]AS DonorName,[DonRegFam].[FundID],
[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM ([DonRegFam] INNER JOIN
[Funds] ON [DonRegFam].[FundID] = [Funds].[FundID])
INNER JOIN [Families]
ON [DonRegFam].[FamilyID] = [Families].[FamilyID]

UNION ALL SELECT
[Registry].[LastName] & "," & [Registry].[FirstName] AS DonorName,
[DonRegInd].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM ([DonRegInd] INNER JOIN [Funds]
ON [DonRegInd].[FundID] = [Funds].[FundID])
INNER JOIN [Registry]
ON [DonRegInd].[RegistryID] = [Registry].[RegistryID]
ORDER BY [DonorName];

 

[Bill Stanton]

Got it!
Thanks to everyone.
Bill