Sorting memo fields Oracle longs (A97/SR2)

Topic

New Reply

Is there anyway to sort linked Oracle Long fields
– I get an ORA-00997 error, illegal use of LONG datatype whenever I try
Ditto with group and count

Reply | Quote

Replies

This is strictly a guess, as I haven’t worked with Oracle in a very long time, but . . . .
I suspect the problem lies with the Oracle ODBC driver and how it deals with long fields – by long fields I presume Access thinks they are memo fields when you look at the linked table in design mode. Access can only sort on up to 255 characters in a field, so you might try creating a calculated field (expression) that takes the left 255 characters – Left(MyField,255) – and then sorting on that. Hope the assumptions are on target, and this helps some.

Reply | Quote

Thanks for the response Wendell
Sort of half the answer, seems even Oracle can’t sort or group the Long fields, tried going in via SQL*Plus, and it wouldn’t do it
But I did find that the field I thought was a Long, and being converted by Access to a Memo, was in fact a VCHAR2(2000). Tried doing a Left() on the field, but got a different error from Access this time, even tried wrapping in a CStr(), still get error
Last thought I had was create a View in Oracle and have it do whatever the Oracle equivalent of Left$ is
Not tried this as it’s home time so will try if I can tomorrow

Reply | Quote