convert memo field to text and eliminate excess spaces

Question

Hi,I tried looking this up in google, but couldnt find any anything... I am running Access 2007/XP/SQLBASE and need to make a query that converts a memo field to text and eliminaes any spaces more than 2 spaces long. Please give me the SQL, based on my snippet below. the memo field is called "BITS". I used to be able to accomplish this using the "SL" function, but it doesnt seem to work in Access2007 but does work in Access 2000. thanks you very much for your help `

SELECT SYSADM_CUST_ORDER_BINARY.CUST_ORDER_ID, SYSADM_CUST_ORDER_BINARY.BITS
FROM SYSADM_CUST_ORDER_BINARY
GROUP BY SYSADM_CUST_ORDER_BINARY.CUST_ORDER_ID, SYSADM_CUST_ORDER_BINARY.BITS;

Answer 1

You can use Replace in Access 2007:

SELECT SYSADM_CUST_ORDER_BINARY.CUST_ORDER_ID,
    Replace(SYSADM_CUST_ORDER_BINARY.BITS & "", "  ", " ")
FROM SYSADM_CUST_ORDER_BINARY 
GROUP BY SYSADM_CUST_ORDER_BINARY.CUST_ORDER_ID, 
    Replace(SYSADM_CUST_ORDER_BINARY.BITS & "", "  ", " ") 

I have never come across SL.