S
Sally
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
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