SQL query view from MS Access. DELETE tbOptions.Value FROM tbOptions WHERE (((tbOptions.Value)=""));
Casey
"Smith,
Matthew P To: SQL <[EMAIL PROTECTED]>
-CONT(DYN)" cc:
<Matthew.P.Sm Subject: access sql help, two questions
ith
@cnet.navy.mi
l>
11/05/02
11:51 AM
Please
respond to
sql
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
Get the mailserver that powers this list at http://www.coolfusion.com
