append update delete query code question

[Guest]

Hi all my experts,

I want to write codes for a series of append/update/delete queries in order
to distinguish "Investors" from regular home buyers given a table of property
ownership information. Prior to this, I created a large number of individual
queries to clean the records, and then ran these action queries through a
form. It worked fine, but it would require more than 50 different queries to
complete this. So I tried to write some RunSQL codes for the exact same
process, but I keep getting error message of either type mismatch or syntax
error.

Can someone please help me? Thank you all!

Sally
**********

Sub InvestorIdentifier()

'copy aoextract3 into aoextract1
DoCmd.CopyObject "", "aoextract1", acTable, "aoextract3"

Dim strSQL As String
DoCmd.SetWarnings False

'delete all records in aoextract2
strSQL = "DELETE aoextract2.* FROM aoextract2;"
DoCmd.RunSQL strSQL

'append aoextract2 with records where Owner name contains "Corporation"
strSQL = "INSERT INTO aoextract2 ( PARCEL, OWNER, ADDRESS1, REMARK ) " & _
"SELECT aoextract1.PARCEL, aoextract1.OWNER, aoextract1.ADDRESS1, REMARK " & _
"FROM aoextract1 " & _
"WHERE (((aoextract1.OWNER) Like " * CORPORATION * " ));"
DoCmd.RunSQL strSQL

'update aoextract2.REMARK with the identified entity
strSQL = "UPDATE aoextract2 SET aoextract2.REMARK = 'CORPORATION' " & _
"WHERE (((aoextract2.REMARK) Is Null));"
DoCmd.RunSQL strSQL

'delete investor records in aoextract1
strSQL = "DELETE aoextract2.* FROM aoextract1 " & _
"WHERE(((aoextract1.OWNER) Like " * CORPORATION * " )) ;"
DoCmd.RunSQL strSQL

'there are a number of similar append/update/delete queries here omitted

DoCmd.SetWarnings True

End Sub

 

[Gary Walter]

A few obvious things...

1) you need to use single quotes within
double-quote limited string

"WHERE (((aoextract1.OWNER) Like " * CORPORATION * " ));"

should be

"WHERE (((aoextract1.OWNER) Like ' * CORPORATION *'' ));"

2) your delete mentions "2"?

strSQL = "DELETE aoextract2.* FROM aoextract1 " & _

I believe you wanted

strSQL = "DELETE aoextract1.* FROM aoextract1 " & _

or just

strSQL = "DELETE * FROM aoextract1 " & _

After each strSQL assignment, you temporarily stick
in a line to print string to the Immediate window.

Debug.Print strSQL

this has helped me many times.

 

[Gary Walter]

A few obvious things...

1) you need to use single quotes within
double-quote limited string

"WHERE (((aoextract1.OWNER) Like " * CORPORATION * " ));"

should be

"WHERE (((aoextract1.OWNER) Like ' * CORPORATION * ' ));"

or replace single double-quote w/ 2 double-quotes

"WHERE (((aoextract1.OWNER) Like "" * CORPORATION * "" ));"


2) your delete query mentions "2"?

strSQL = "DELETE aoextract2.* FROM aoextract1 " & _

I believe you wanted

strSQL = "DELETE aoextract1.* FROM aoextract1 " & _

or just

strSQL = "DELETE * FROM aoextract1 " & _

After each strSQL assignment, you can temporarily stick
in a line to print the SQL string to the Immediate window.

Debug.Print strSQL

this has helped me many times.

(copy the string from the Immediate Window into SQL View
of new query to test what might be wrong if not obvious)