Unwanted alphanumeric sort on time, incorrect min/max value

[Jelle]

LS,

query below is sorted on order and date ascending. The date fields are, as
far as I know real date fields, still, the sort appears to be alphanumeric,
which screws up a min max on the query as well.

Any suggestions??



ID UNIQUE_KEY ORDER Assignment aanmaak Assignment
31229 33238020 NH38650 03/05/2008 8:57:17 03/05/2008 10:41:43
31210 33237654 NH38650 03/05/2008 8:57:17 03/05/2008 9:23:00
31221 33237778 NH38650 03/05/2008 8:57:17 03/05/2008 9:37:34

 

[Bob Barrows [MVP]]

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.

 

[Jelle]

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]]

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.

Here is the result again:
ID UNIQUE_KEY ORDER Assignment aanmaak Assignment
31229 33238020 NH38650 03/05/2008 8:57:17 03/05/2008 10:41:43
31210 33237654 NH38650 03/05/2008 8:57:17 03/05/2008 9:23:00
31221 33237778 NH38650 03/05/2008 8:57:17 03/05/2008 9:37:34

Well, I still have my doubts about the data type of the "Assignment
gereed" field: could you resolve those doubts?
You could be explicit about it: change the order by to

ORDER BY IIf([ORDER_ID]<>"",[ORder_id],[id]),
Cdate(tblMAHIS_WO.[Assignment gereed]);

 

[Jelle]

Bob,

the field mentioned is a downloaded DateTime field from a WMS-system. The
Cdate fucntion solves the issue. Thx!

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.

Here is the result again:
ID UNIQUE_KEY ORDER Assignment aanmaak Assignment
31229 33238020 NH38650 03/05/2008 8:57:17 03/05/2008 10:41:43
31210 33237654 NH38650 03/05/2008 8:57:17 03/05/2008 9:23:00
31221 33237778 NH38650 03/05/2008 8:57:17 03/05/2008 9:37:34

Well, I still have my doubts about the data type of the "Assignment
gereed" field: could you resolve those doubts?
You could be explicit about it: change the order by to

ORDER BY IIf([ORDER_ID]<>"",[ORder_id],[id]),
Cdate(tblMAHIS_WO.[Assignment gereed]);

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.