Special syntax for ORDER BY ?????

  • Thread starter Thread starter Bill Stanton
  • Start date Start date
B

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];
 
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],
Click to expand...
[Type]
FROM ([DonRegInd] INNER JOIN [Funds]
ON [DonRegInd].[FundID] = [Funds].[FundID])
INNER JOIN [Registry]
ON [DonRegInd].[RegistryID] = [Registry].[RegistryID]
ORDER BY [DonorName];


.
Click to expand...
 
Try the following UNDOCUMENTED and probably UNSUPPORTED Order by clause.

ORDER BY 1

You just specify the column number.
 
[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]
 
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


Jason said:
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],
Click to expand...
[Type]
FROM ([DonRegInd] INNER JOIN [Funds]
ON [DonRegInd].[FundID] = [Funds].[FundID])
INNER JOIN [Registry]
ON [DonRegInd].[RegistryID] = [Registry].[RegistryID]
ORDER BY [DonorName];


.
Click to expand...
Click to expand...
 
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



John Spencer (MVP) said:
Try the following UNDOCUMENTED and probably UNSUPPORTED Order by clause.

ORDER BY 1

You just specify the column number.


Bill said:
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];
Click to expand...
Click to expand...
 
Back
Top