RE: access sql help, two questions

[Andy Ewings]

Is the field empty or does it contain NULLS?

If it contains Nulls you need.....

DELETE
FROM tbOptions
WHERE value IS NULL

OR try 

DELETE
FROM tbOptions
WHERE value = NULL

-----Original Message-----
From: Smith, Matthew P -CONT(DYN) [mailto:Matthew.P.Smith@;cnet.navy.mil]
Sent: 05 November 2002 17:52
To: SQL
Subject: 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
Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm