cannot order invalid datetime records

[Davros]

hi
short history; we are trying to migrate date from an oracle database via access but are having problems manipulating the data - we cannot copy it, paste it, make tables from queries [and if we do, we have an ODBC failure mid way through creating a table from a query]. obviously the back end has been locked up so tight that we cannot get into it.
anyway we have found some workarounds. one of which concerns the date time format which is slightly different between oracle and access. using the length criteria in an access query {len([fieldname])} I can tell if a record is 10characters long or 19 characters long. it is the 19 characters long records that we need to change.
the problem is that we cannot order the character column {length} to sort descending so that the records that are 19 characters long are on top. i am absolutely stumped as to why this is - 19 is greater then 10 so why can't we sort them in descending order? the end result is that we have a method for identifying these records but we canot sort them out from other records.
has anyone any ideas why this is and do you know of a workaround??

thanks in advance

 

[RuralGuy]

Have you examined the 19 character field so you know what each character is? Can you post the format of the 19 character field?

 

[Davros]

wow fast reply ruralguy
basically the short date field in access is dd/mm/yyyy BUT in the oracle datebase its been formated to dd/mm/yyyy tt:tt:tt [where t is the actual time]. i'm no expert in this but we cannot sort out the records that have date time that is 10 characters long and those which are 19 characters long even though they are in the same field.
we also cannot get into the oracle database to see what the field or table property is.
very strange?

 

[RuralGuy]

If they are coming over as actual Access DateTime fields then this link should be helpful. If they are Text fields then that is a different story.