Deleting Records from an Access Database

[sysadmin07]

Hi,

I'm trying to delete multiple rows from an Access database. I'm having trouble figuring out how to call entries that are differentiated by -1, -2, -3, etc. (outlined below under the "Name" heading). How would I go about deleting the John-1, John-2, and John-3 rows? FYI, this is a rather large database and I've listed a few test entries below, so please don't question the logic of its structure:

Name Job Description
John-1 Clerical
John-2 Reception Desk
Mary-1 Custodial
John-3 Accounting

Here's the start of my delete code:

<CFQUERY NAME="deleteItem" DATASOURCE="test">
DELETE * FROM tableJobs
WHERE name='#????????#';
</CFQUERY>

Thank you

 

[r937]

WHERE name LIKE 'John*'

alternatively

WHERE LEFT(name,4) = 'John'

r937.com | rudy.ca

 

[cfStarlight]

Assuming the first part of the name is known

<CFSET FirstNamePart = "John">
<CFQUERY NAME="deleteItem" DATASOURCE="test">
DELETE * FROM tableJobs
WHERE name ='#FirstNamePart#-%';
</CFQUERY>

 

[r937]

is the wildacrd % or * ??

this is msaccess, and yet i know there's a difference between AOD and DOA... or is it DAO and AOD...

oh dear...

r937.com | rudy.ca

 

[cfStarlight]

I think its "*" if you're querying directly in Access (or possibly using ADO? Not sure?

And its "%" if you're using cfquery (ie ODBC or JDBC-ODBC)

(good grief. Could we possibly have any more acronyms?