There are different forms of what you call "empty". If no value was inserted, the value is NULL.
Try DELETE FROM tbOptions WHERE value IS NULL Regards Thomas ------------------------------------------- Thomas Eppler Internet Management Projekte f�r vernetzte Kommunikation, Kollaboration und Projektarbeit im Unternehmen zur F�rderung von Wissensaustausch, situativem Lernen und kollaborativem Arbeiten Postadresse: Grossackerstrasse 65, CH - 8041 Z�rich Email: [EMAIL PROTECTED] Web: http://www.internetmanagement.ch/ -----Urspr�ngliche Nachricht----- Von: Smith, Matthew P -CONT(DYN) [mailto:Matthew.P.Smith@;cnet.navy.mil] Gesendet: Dienstag, 5. November 2002 18:52 An: SQL Betreff: access sql help, two questions I'm used to MS SQL, so I'm having a bit of trouble with MS Access sql syntax. I want to delete all records where a certain field is empty. I have tried below, but it doesn't work. DELETE FROM tbOptions WHERE value = "" (also tried single quotes) DELETE * FROM tbOptions WHERE value = '' (also tried) DELETE * FROM tbOptions WHERE len(value) = 0 Second question: I often do several SQL statements in a cfquery tag when hitting ms sql(see bottom). Is it possible in access to do the same? What about loops and conditions, t-sql? I have tried putting a ";" after each statement, but that errors out. Thanks for any help, Matthew P. Smith Web Developer, Object Oriented Naval Education & Training Professional Development & Technology Center (NETPDTC) (850)452-1001 ext. 1245 [EMAIL PROTECTED] <cfquery name="qry_allTaxologyKeysBelow" connectstring="#request.dbConnectionString#" dbtype="dynamic"> SET NOCOUNT ON DECLARE @tblVar#listFirst(GetFileFromPath(GetCurrentTemplatePath()), ".")# TABLE ( tablePK int identity(1,1), tblProductTaxologyPK varchar(10) ) DECLARE @continue int INSERT INTO @tblVar#listFirst(GetFileFromPath(GetCurrentTemplatePath()), ".")# SELECT tblProductTaxologyPK FROM #request.sqlObjectPrefix#tblProductTaxology WHERE parentFK = <cfqueryparam value="#attributes.taxologyKeyList#" cfsqltype="CF_SQL_INTEGER"> SET @continue = @@ROWCOUNT WHILE @continue > 0 BEGIN INSERT INTO @tblVar#listFirst(GetFileFromPath(GetCurrentTemplatePath()), ".")# SELECT tblProductTaxologyPK FROM #request.sqlObjectPrefix#tblProductTaxology WHERE parentFK IN ( SELECT DISTINCT tblProductTaxologyPK FROM @tblVar#listFirst(GetFileFromPath(GetCurrentTemplatePath()), ".")# ) AND tblProductTaxologyPK NOT IN ( SELECT DISTINCT tblProductTaxologyPK FROM @tblVar#listFirst(GetFileFromPath(GetCurrentTemplatePath()), ".")# ) SET @continue = @@ROWCOUNT END SET NOCOUNT OFF SELECT * FROM @tblVar#listFirst(GetFileFromPath(GetCurrentTemplatePath()), ".")# </cfquery> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=6 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=6 Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
