SQL - Where clause

  • Thread starter Thread starter Ginger
  • Start date Start date
G

Ginger

I have 2 "INSERT INTO" statements as shown below. I have a
recordset that opens and the code loops through the records
one at a time. Problem is that the one from the employee
file does work, and the one from the manager file errors
out. Can anyone see what syntax mistake I've made on the
first statement for the managers? I've looked and looked
and still don't see it.

What I'm trying to do is have a report containing only the
data necessary for each record be sent to that person by email.

Thank you in advance!

Ginger


This is the one from the manager file

qry1 = "INSERT INTO tbl_Monthly_File_Email_Branch (branch,
manager, [Email Address])"
& "SELECT tbl_incentive.org_code, tbl_orgcode.manager,
tbl_orgcode.email" _
& " FROM tbl_orgcode INNER JOIN tbl_incentive ON
tbl_orgcode.org_code = tbl_incentive.org_code" _
& WHERE ((tbl_orgcode.org_code) = """ & rs!OrgCode & """)" _
& " AND tbl_orgcode.email Is Not Null" _
GROUP BY tbl_incentive.orgcode, tbl_orgcode.manager,
tbl_orgcode.email"
DoCmd.RunSQL (qry1)


This is the one from the employee file

qry1 = "INSERT INTO
tbl_Monthly_File_Email_Employee(OrgCode, SSN, Employee,
[Email Address])"
& "SELECT tbl_incentive.org_code, ssn, emp_name, email" _
& " FROM tbl_incentive INNER JOIN tbl_emp_master ON
(tbl_incentive.org_code) = tbl_emp_master.org_code" _
& WHERE ((tbl_emp_master.ssn) = """ & rs!ssn & """)" _
& " AND tbl_emp_master.email Is Not Null" _
GROUP BY tbl_incentive.orgcode, tbl_emp_master.ssn,
tbl_emp_master.emp_name, tbl_emp_master.email"
DoCmd.RunSQL (qry1)
 
Ginger said:
I have 2 "INSERT INTO" statements as shown below. I have a
recordset that opens and the code loops through the records
one at a time. Problem is that the one from the employee
file does work, and the one from the manager file errors
out. Can anyone see what syntax mistake I've made on the
first statement for the managers? I've looked and looked
and still don't see it.

What I'm trying to do is have a report containing only the
data necessary for each record be sent to that person by email.

This is the one from the manager file

qry1 = "INSERT INTO tbl_Monthly_File_Email_Branch (branch,
manager, [Email Address])"
& "SELECT tbl_incentive.org_code, tbl_orgcode.manager,
tbl_orgcode.email" _
& " FROM tbl_orgcode INNER JOIN tbl_incentive ON
tbl_orgcode.org_code = tbl_incentive.org_code" _
& WHERE ((tbl_orgcode.org_code) = """ & rs!OrgCode & """)" _
& " AND tbl_orgcode.email Is Not Null" _
GROUP BY tbl_incentive.orgcode, tbl_orgcode.manager,
tbl_orgcode.email"
DoCmd.RunSQL (qry1)


This is the one from the employee file

qry1 = "INSERT INTO
tbl_Monthly_File_Email_Employee(OrgCode, SSN, Employee,
[Email Address])"
& "SELECT tbl_incentive.org_code, ssn, emp_name, email" _
& " FROM tbl_incentive INNER JOIN tbl_emp_master ON
(tbl_incentive.org_code) = tbl_emp_master.org_code" _
& WHERE ((tbl_emp_master.ssn) = """ & rs!ssn & """)" _
& " AND tbl_emp_master.email Is Not Null" _
GROUP BY tbl_incentive.orgcode, tbl_emp_master.ssn,
tbl_emp_master.emp_name, tbl_emp_master.email"
DoCmd.RunSQL (qry1)
Click to expand...


A couple of observations.

You're missing a quote at the start of the WHERE clause:

& " WHERE ((tbl_emp_master.ssn) = """ & rs!ssn & """)" _
& " AND tbl_emp_master.email Is Not Null" _

The GROUP BY clause is also missing quotes around it. Unless
its purpose is to suppress duplicate records, try just
getting rid of it.

You also may be missing some spaces at the end or beginning
of some lines. I think the above code would generate one or
more compile errors, so be sure to compile the module before
attempting to execute it.

A general debugging texhnique for this kind of thing is to
add the statement:
Debug.Print sql1
just before the RunSQL line so you can see the results of
all that concatenation in the Immediate Window.
 
After you've corrected any spacing errors and since a SSN field should be text, try to use the following last two lines of the SQL statement

& " WHERE tbl_emp_master.ssn = '" & rs!ssn & "'"
& " AND tbl_emp_master.email Is Not Null "

Note the single quote versus the double quote
Bob
----- Ginger wrote: ----

I have 2 "INSERT INTO" statements as shown below. I have
recordset that opens and the code loops through the record
one at a time. Problem is that the one from the employe
file does work, and the one from the manager file error
out. Can anyone see what syntax mistake I've made on th
first statement for the managers? I've looked and looke
and still don't see it

What I'm trying to do is have a report containing only th
data necessary for each record be sent to that person by email

Thank you in advance

Ginge


This is the one from the manager fil

qry1 = "INSERT INTO tbl_Monthly_File_Email_Branch (branch
manager, [Email Address])
& "SELECT tbl_incentive.org_code, tbl_orgcode.manager
tbl_orgcode.email"
& " FROM tbl_orgcode INNER JOIN tbl_incentive O
tbl_orgcode.org_code = tbl_incentive.org_code"
& WHERE ((tbl_orgcode.org_code) = """ & rs!OrgCode & """)"
& " AND tbl_orgcode.email Is Not Null"
GROUP BY tbl_incentive.orgcode, tbl_orgcode.manager
tbl_orgcode.email
DoCmd.RunSQL (qry1


This is the one from the employee fil

qry1 = "INSERT INT
tbl_Monthly_File_Email_Employee(OrgCode, SSN, Employee
[Email Address])
& "SELECT tbl_incentive.org_code, ssn, emp_name, email"
& " FROM tbl_incentive INNER JOIN tbl_emp_master O
(tbl_incentive.org_code) = tbl_emp_master.org_code"
& WHERE ((tbl_emp_master.ssn) = """ & rs!ssn & """)"
& " AND tbl_emp_master.email Is Not Null"
GROUP BY tbl_incentive.orgcode, tbl_emp_master.ssn
tbl_emp_master.emp_name, tbl_emp_master.email
DoCmd.RunSQL (qry1
 
Back
Top