Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting memo fields Oracle longs (A97/SR2)

    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
    <img src=/S/help.gif border=0 alt=help width=23 height=15>

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Sorting memo fields Oracle longs (A97/SR2)

    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.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting memo fields Oracle longs (A97/SR2)

    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 <img src=/S/frown.gif border=0 alt=frown width=15 height=15>
    Last thought I had was create a View in Oracle and have it do whatever the Oracle equivalent of Left$ is <img src=/S/thinks.gif border=0 alt=thinks width=15 height=15>
    Not tried this as it's home time so will try if I can tomorrow <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •