Sorting Memo datatype

  • Thread starter Thread starter Barb@mmcpa
  • Start date Start date
B

Barb@mmcpa

I am using an SQL table to store my data. I am using
datatype text for one of the fields in the SQL table. In
Access this translates to Memo datatype. When I do a
query and try to sort by this field I get the message: The
text data type can not be used in an ORDER BY clause.

I have tried using the data type char in the SQL table but
that fills area of the field that doesn't have a character
with a blank character. This has caused some issues when
we try to make any changes to the field.

Does anyone have any suggestions what I can do?

Thanks!
 
Barb@mmcpa said:
I am using an SQL table to store my data. I am using
datatype text for one of the fields in the SQL table. In
Access this translates to Memo datatype. When I do a
query and try to sort by this field I get the message: The
text data type can not be used in an ORDER BY clause.

I have tried using the data type char in the SQL table but
that fills area of the field that doesn't have a character
with a blank character. This has caused some issues when
we try to make any changes to the field.

Does anyone have any suggestions what I can do?
Click to expand...

Use VARCHAR instead of CHAR data type.
 
Use a field type of VarChar in SQL if you don't want trailing spaces. Char is a
fixed-length field.

OR

You can sort by first 255 characters of the memo field using the Left function.

ORDER BY Left(TheField,255)

In the query grid

Field: ForceSort: Left(YourMemoField,255)
Sort: Ascending
 
I agree that using VARCHAR is the way to go to eliminate the blank
characters, but believe this will also be interpreted as a memo data type in
Access.

Whenever I want to do this, I use the following

ORDER BY CStr(NZ([YourField],""));

This has worked for me, but I don't know at what point (number of characters
in the field) the CSTR() conversion function fails.

HTH
Dale
 
Back
Top