Unwanted alphanumeric sort on time, incorrect min/max value


J

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
 
Ad

Advertisements

B

Bob Barrows [MVP]

Jelle said:
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.
 
J

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
 
B

Bob Barrows [MVP]

Jelle said:
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]);
 
Ad

Advertisements

J

Jelle

Bob,

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



Bob Barrows said:
Jelle said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top