Re: Unwanted alphanumeric sort on time, incorrect min/max value

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Bob,

hereby the code:

SELECT tblMAHIS_WO.ID, tblMAHIS_WO.UNIQUE_KEY,
IIf([ORDER_ID]<>"",[ORder_id],[id]) AS [ORDER], tblMAHIS_WO.[Assignment
aanmaak], tblMAHIS_WO.[Assignment gereed], tblMAHIS_WO.Van, tblMAHIS_WO.Naar,
tblMAHIS_WO.ASSIGNMENT_ID, tblMAHIS_WO.TU_ID
FROM tblMAHIS_WO
WHERE (((tblMAHIS_WO.Naar)="WO_COMPL_PAL"))
ORDER BY IIf([ORDER_ID]<>"",[ORder_id],[id]), tblMAHIS_WO.[Assignment gereed];

The iff-clause is to ensure uniqueness for those records that do not belong
to an order. I assume this should not be the problem ast the sort within the
order is the issue.

Thx, Jelle



"Bob Barrows [MVP]" wrote:

Jelle wrote:
LS,

query below is sorted on order and date ascending.

Actually, what I see below is what I assume to be the result of running a
query. It is not the query itself. So ...

Please show us the sql of the query being used to generate this data (you
can clip out the parts we don't need to see - it's the ORDER BY clause i am
interested in). To see the sql, open your query in Design mode, then switch
to SQL View using the View menu or the toolbar button (you can also switch
using the right-click context menu)

The date fields
are, as far as I know real date fields,

"as far as I know"?
I'm not clear why there would be any doubt in your mind, which leads to my
next question:
Is this a local table in your mdb file? or a remote, linked table from
another database? If it is a remote table, please tell us the type and
version of the remote database, as well as the exact data types of the
fields involved. Actually, even if it is a local table, you should open the
table in Design mode and look at the data types (I'm not interested in the
format) of those "date" fields and let us know what they are, for sure.

still, the sort appears to be
alphanumeric, which screws up a min max on the query as well.

Any suggestions??

I will let you know when you have provided the extra information.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"



.

Relevant Pages

  • Re: Changing Query behavior based on local vs. remote context?
    ... Sounds like larger network packets are getting dropped. ... Senior SQL Infrastructure Consultant ... When I run queries against the installed instance I get different behavior depending on if the query is run from Management Studio locally or from SSMS running on a remote machine. ...
    (microsoft.public.sqlserver.security)
  • Re: how to know if query is processed in remote server
    ... One of the steps in execution plan shows "Remote Query". ... the data has been selected on remote server before it's pulled over to ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.programming)
  • Re: Query not finding combo box parameter
    ... Here's the sql of a simple select query qryAvebyBlock&Week with ... If you copied & pasted the SQL from your query's SQL View there should ... If the data types of the ComboBoxes is different be sure to change the ...
    (microsoft.public.access.queries)
  • Possible?? SQL Svr obtaining data via SOAP/XML from another SQL Sv
    ... SQL servers (one local, one remote through the internet). ... I used VBScript, MSXML, MSSOAP to connect, query, parse and insert data into ... XML" functionality provided in SELECT verb. ...
    (microsoft.public.sqlserver.xml)
  • Re: SQL Error Dump
    ... Can you try running the query using either STRSQL or iSeries Navigator Run SQL Script? ... you might check that the data types of host variables are compatible with the result set data types for corresponding columns. ... other SQL statements from being processed. ...
    (comp.sys.ibm.as400.misc)