Rx_
Nothing In Moderation
- Local time
- Yesterday, 20:27
- Joined
- Oct 22, 2009
- Messages
- 2,803
Before anyone responded, I just removed the Converts.
With a ODBC connection to the View, I kind of expected the sort orders to be valid.
Since they are not, I will manage this at the VBA level.
Let the lesson be that a Convert (varchar....) does exactly that as advertised.
While the linked table of a view is much faster than passing a SQL statement, it appears that Access SQL must be used to obtain the date order.
From SQL, the view results appears in the correct order.
It appears that something is lost in the ODBC (SQL Native Client) layer.
What function in T-SQL or MS SQL will keep the date format, but not show the time portion of the date - so the view can be sorted by date on one column?
This conversion prevents sorting by Year-Month-Date
A set of data has dates that need to be sorted by date on one field.
A View was created. The view becomes a Linked Table in Access.
The dates all had the full time format for the date and hour format.
e.g. 5/9/14 12:00PM
Only the date was needed. So the CONVERT function was used. At first appearance, it seemed to make a short date format: e.g. 01/11/2014
But, the sort is all on the first two characters (the day for us US types) instead of the actual date.
The linked tables show the format as Text.
With a ODBC connection to the View, I kind of expected the sort orders to be valid.
Since they are not, I will manage this at the VBA level.
Let the lesson be that a Convert (varchar....) does exactly that as advertised.
While the linked table of a view is much faster than passing a SQL statement, it appears that Access SQL must be used to obtain the date order.
From SQL, the view results appears in the correct order.
It appears that something is lost in the ODBC (SQL Native Client) layer.
What function in T-SQL or MS SQL will keep the date format, but not show the time portion of the date - so the view can be sorted by date on one column?
This conversion prevents sorting by Year-Month-Date
A set of data has dates that need to be sorted by date on one field.
A View was created. The view becomes a Linked Table in Access.
The dates all had the full time format for the date and hour format.
e.g. 5/9/14 12:00PM
Only the date was needed. So the CONVERT function was used. At first appearance, it seemed to make a short date format: e.g. 01/11/2014
But, the sort is all on the first two characters (the day for us US types) instead of the actual date.
The linked tables show the format as Text.
Code:
SELECT TOP (100) PERCENT ID_Wells, txtFedStCo AS RE_24Type, CONVERT(varchar, Dt_APD_Sub, 101) AS RE_24DTSub, txt_APD_Apv_Status AS RE_24Status,
CONVERT(varchar, Dt_APD_Apv, 101) AS RE_24DTApp, CONVERT(varchar, Dt_APD_Exp, 101) AS RE_24DTExp, CONVERT(varchar, Dt_APD_WithDrawn_Sub, 101)
AS RE_24DTWDSub, CONVERT(varchar, Dt_APD_WithDrawn_APProved, 101) AS RE_24DTWDApp
FROM dbo.tblAPD_Fed_ST_CO
WHERE (txtFedStCo IN (N'Fed')) AND (txt_APD_Apv_Status = N'Approved') AND (NOT (CONVERT(varchar, Dt_APD_Sub, 101) IS NULL))
ORDER BY ID_Wells, RE_24DTSub DESCAttachments
-
Convert doesn't sort by Year-Date-day.png
Last edited:
