Re: SQL Order by Date `Date` column CAP ATTACHED 32kb

Grrrrrr!

You've missed the point again.

We are not talking about any FORMAT.

We are talking about the DATATYPE.

The FORMAT only has to do with how something is displayed. It has nothing to
do with how it is stored or sorted.

When you open the table in design mode you are present with a grid in which
each field is represent as a row. There are normally 3 columns. The column
on the left has the caption 'Field Name', the column on the right has the
caption 'Description'. The other column has the caption 'Data Type'. That is
one we are interested in. Forget about the panel(s) down the bottom - they
have nothing to do with it.

One of the rows in the grid has 'date' in the 'Field Name' column. If that
row also has 'Date/Time' in the 'Data Type' column then "... order by
[date]" will sort the results the way you want it. If it hasn't then it
won't. It is as simple as that.

Everything you have described indicates that, as another responder said, you
actually have Test that looks like a date, in which case, the sort WILL
cause the results you say you are getting.

The people who responded to your posts do know what they are talking about,
and your apparent obtuseness very likely to be taken as an insult.


"Edward Boquette" <uwhat@xxxxxxxxxxxxxx> wrote in message
news:4gv3e.6990668$f47.1282971@xxxxxxxxxxxxxxxxxxxx
>
> "Stephany Young" <noone@localhost> wrote in message
> news:Ocbgey1NFHA.1500@xxxxxxxxxxxxxxxxxxxxxxx
>> The question that people are really asking you is:
>>
>> What datatype is the [date] column defined as in the actual table in
>> the
>> actual database?
>>
>> If the datatype is Date/Time then "... order by [date]" is the correct
>> SQL
>> syntax to return the recordset in order of [date] ascending.
>>
>> If the datatype is Text then you will have to convert the value to a
>> Date/Time to sort it. With SQL Server you would use " ... order by
>> cast([date] as datetime)". With Jet, if I remember rightly, you can use "
>> order by CDate([date])" but don't quote me on that.
>>
>>
>
> Hi Ya
>
> You had the answer i was looking for yesterday,
> The other piece of sort code i wrote is producing the same results, bit
> like
> i said its fiddly!!!
>
> The datatype in my MS Access DB was in date/time format but still didint
> work when i sorted it, so tried Text and still no good with the normal
> sort
> code.
>
> It was left in text mode last night and worked with my code , and also
> works
> with the code above so will leave it in text mode now !!
>
> Many thanks again
>
> Ed.
>
>
>
>


.

Relevant Pages

  • Re: Completely baffled on what should be simple
    ... the same thing I previously did, changing format to custom ... sort, it didn't work. ... if a cell contains the text ...
    (microsoft.public.excel.misc)
  • Re: Completely baffled on what should be simple
    ... And remember just changing the format of the cell won't change the underlying ... sort, it didn't work. ... for the extensions that have either one or two 0's as the first numbers, ...
    (microsoft.public.excel.misc)
  • Re: Global Variables
    ... The watch window says "Expression not defined in context". ... far as actually getting the Lusedrow and Nlusedrow for 'Annuity'. ... I had code to sort ... Call CrackedID(OldShName, FileName, DataType, StartCol, _ ...
    (microsoft.public.excel.programming)
  • Re: Global Variables
    ... I found the sort problem and it has been fixed. ... far as actually getting the Lusedrow and Nlusedrow for 'Annuity'. ... 'Advisor' ran all the way through but errored in 'Annuity' Lsuedrow instead. ... Call CrackedID(OldShName, FileName, DataType, StartCol, _ ...
    (microsoft.public.excel.programming)
  • Re: Sane Syntax
    ... It's sort of like a game of telephone - if converting ... I don't know much about DocBook, but tsy's "Super DocBook" format does ... capability for visual markup, ... material before it was typeset. ...
    (comp.text.tex)