Subqueries and numeric to character conversion problem

Greetings, I have a problem and I am hoping someone can tell me what I am doing wrong and perhaps enlighten me on why the following doesn't work. I spent about 2 hours yesterday trying to understand why it doesn't, gave up and I'm turning to the gurus. I have many tables in a database that have the following format %YYYYMMDDCC where % = a single character; YYYY = year; MM = Month; DD = day; CC = cycle. So for today, I have the following tables: a1997072100 b1997072100 c1997072100 . . . etc. I want to drop tables that are "old" based on the date in the table name. However, I don't want to drop tables that have a "lock" on them. That information is stored in another table as an integer of the format YYYYMMDDCC. So I have the following SQL to return a list of YYYYMMDDCC tables in the database: SELECT DISTINCT tabname[2,11] FROM systables WHERE tabname[2,11] MATCHES "[1-2]??[0-9]*"; (1) This returns something like: 1997072100 1997072012 1997072000 1997071912 1997071900 1997071812 The query for the lock table is simple. SELECT DISTINCT date_cycle FROM lock_table; (2) This returns: 1997071912 1997072012 Simply combining (1) and (2) with (2) as a subquery to the first one doesn't work. I get a -1213. Okay, so I put quotes around date_cycle: SELECT DISTINCT "'"||date_cycle||"'" FROM lock_table; (3) This returns: '1997071912' '1997072012' The combined SELECT statement now looks like this: SELECT DISTINCT tabname[2,11] FROM systables WHERE tabname[2,11] MATCHES "[1-2]??[0-9]*" AND tabname[2,11] NOT IN ( SELECT DISTINCT "'"||date_cycle||"'" FROM lock_table ); (4) But it still returns all the rows of query (1)! If I remove the subquery and put the date_cycles (with quotes) in by hand it works fine, i.e. the locked tables aren't returned: SELECT DISTINCT tabname[2,11] FROM systables WHERE tabname[2,11] MATCHES "[1-2]??[0-9]*" AND tabname[2,11] NOT IN ( '1997071912', '1997072012' ); Hence my "why" question. I suspect I am very close to getting it right with (4) or that it cannot be done this way at all. Using DB-Access Version 5.03.UC1 TIA, mgo p.s. I apologize if this was posted twice. I didn't recieve any response to my original post. I suspect that my news server didn't post it, hence this time I'm using Deja News. -------------------------------------------------------------- Mark Oberfield E-mail: oberfiel@storm.nws.noaa.gov -------------------------------------------------------------- -------------------==== Posted via Deja News ====----------------------- http://www.dejanews.com/ Search, Read, Post to Usenet