Send Object VBA Question

G

Guest

I have the following code which is for looping through each department
(Approximately 200) and sending a separate e-mail to the recipient listed in
a report. I am getting an error 3075( Missing operator) on line qdf.SQL =
strSQL and can't figure out the issue. VBA newbie still trying to figure out
how to create bug free code. Thanks in advance for any assistance you can
offer.

Private Sub cmdEmailReports_Click()
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strBaseSQL As String
Set qdf = CurrentDb.QueryDefs("qryAGHSvcCode")
strBaseSQL = qdf.SQL
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
With rst
Do Until .EOF
strSQL = Left(strBaseSQL, Len(strBaseSQL) - 3)
strSQL = strSQL & " WHERE [Expr1]='" & ![Expr1] & " '"
qdf.SQL = strSQL
DoCmd.SendObject acSendReport, "rptAGHLateCharges",
"SnapshotFormat(*.snp)", !Recipient, , , "YourSubject", "YourMessage", False
.MoveNext
Loop
.Close
End With
qdf.SQL = strBaseSQL
Set qdf = Nothing
Set rst = Nothing

End Sub
 
D

Dirk Goldgar

shoe said:
I have the following code which is for looping through each department
(Approximately 200) and sending a separate e-mail to the recipient
listed in a report. I am getting an error 3075( Missing operator) on
line qdf.SQL = strSQL and can't figure out the issue. VBA newbie
still trying to figure out how to create bug free code. Thanks in
advance for any assistance you can offer.

Private Sub cmdEmailReports_Click()
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strBaseSQL As String
Set qdf = CurrentDb.QueryDefs("qryAGHSvcCode")
strBaseSQL = qdf.SQL
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
With rst
Do Until .EOF
strSQL = Left(strBaseSQL, Len(strBaseSQL) - 3)
strSQL = strSQL & " WHERE [Expr1]='" & ![Expr1] & " '"
qdf.SQL = strSQL
DoCmd.SendObject acSendReport, "rptAGHLateCharges",
"SnapshotFormat(*.snp)", !Recipient, , , "YourSubject",
"YourMessage", False .MoveNext
Loop
.Close
End With
qdf.SQL = strBaseSQL
Set qdf = Nothing
Set rst = Nothing

End Sub
Click to expand...

I don't see the problem offhand. What's the value of strSQL at the
moment you get the error?
 
S

Sylvain Lafontaine

You have used single quote ' instead of double quote " for delimiting your
string expression. The use of the single quote string delimiter is for
SQL-Server; with Access SQL, you must use double quotes instead.

Also, you have added a blank space at the end of your string expression,
inside the delimiters; I'm not sure this is really your intention.
 
G

Guest

How would I change it? I have tried to change the various quotes but nothing
has worked to date. Thanks again for you help.

Sylvain Lafontaine said:
You have used single quote ' instead of double quote " for delimiting your
string expression. The use of the single quote string delimiter is for
SQL-Server; with Access SQL, you must use double quotes instead.

Also, you have added a blank space at the end of your string expression,
inside the delimiters; I'm not sure this is really your intention.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


shoe said:
I have the following code which is for looping through each department
(Approximately 200) and sending a separate e-mail to the recipient listed
in
a report. I am getting an error 3075( Missing operator) on line qdf.SQL =
strSQL and can't figure out the issue. VBA newbie still trying to figure
out
how to create bug free code. Thanks in advance for any assistance you can
offer.

Private Sub cmdEmailReports_Click()
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strBaseSQL As String
Set qdf = CurrentDb.QueryDefs("qryAGHSvcCode")
strBaseSQL = qdf.SQL
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
With rst
Do Until .EOF
strSQL = Left(strBaseSQL, Len(strBaseSQL) - 3)
strSQL = strSQL & " WHERE [Expr1]='" & ![Expr1] & " '"
qdf.SQL = strSQL
DoCmd.SendObject acSendReport, "rptAGHLateCharges",
"SnapshotFormat(*.snp)", !Recipient, , , "YourSubject", "YourMessage",
False
.MoveNext
Loop
.Close
End With
qdf.SQL = strBaseSQL
Set qdf = Nothing
Set rst = Nothing

End Sub
Click to expand...
Click to expand...
 
D

Dirk Goldgar

Sylvain Lafontaine said:
You have used single quote ' instead of double quote " for delimiting
your string expression. The use of the single quote string delimiter
is for SQL-Server; with Access SQL, you must use double quotes
instead.
Click to expand...

I disagree, Sylvain. Access (Jet) SQL accepts both single and double
quotes, though of course you can't use a single quote at one end of a
string literal and a double quote at the other end.
 
S

Sylvain Lafontaine

Want you want to insert a double quote inside a string, you must use either
Chr(34) or use 2 double quotes:

Try:
strSQL = strSQL & " WHERE [Expr1]=""" & ![Expr1] & """"

or

strSQL = strSQL & " WHERE [Expr1]=" & Chr(34) & ![Expr1] & Chr(34)

instead of:
strSQL = strSQL & " WHERE [Expr1]='" & ![Expr1] & " '"


Also, is the expression [Expr1] can contains double quotes, then you must
double them:

.... & Replace (![Expr1], """", """""") & ...

or:

.... & Replace (![Expr1], Chr(34), Chr(34) & Chr(34)) & ...


As you see, this makes good exam questions.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


shoe said:
How would I change it? I have tried to change the various quotes but
nothing
has worked to date. Thanks again for you help.

Sylvain Lafontaine said:
You have used single quote ' instead of double quote " for delimiting
your
string expression. The use of the single quote string delimiter is for
SQL-Server; with Access SQL, you must use double quotes instead.

Also, you have added a blank space at the end of your string expression,
inside the delimiters; I'm not sure this is really your intention.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


shoe said:
I have the following code which is for looping through each department
(Approximately 200) and sending a separate e-mail to the recipient
listed
in
a report. I am getting an error 3075( Missing operator) on line
qdf.SQL =
strSQL and can't figure out the issue. VBA newbie still trying to
figure
out
how to create bug free code. Thanks in advance for any assistance you
can
offer.

Private Sub cmdEmailReports_Click()
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strBaseSQL As String
Set qdf = CurrentDb.QueryDefs("qryAGHSvcCode")
strBaseSQL = qdf.SQL
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
With rst
Do Until .EOF
strSQL = Left(strBaseSQL, Len(strBaseSQL) - 3)
strSQL = strSQL & " WHERE [Expr1]='" & ![Expr1] & " '"
qdf.SQL = strSQL
DoCmd.SendObject acSendReport, "rptAGHLateCharges",
"SnapshotFormat(*.snp)", !Recipient, , , "YourSubject", "YourMessage",
False
.MoveNext
Loop
.Close
End With
qdf.SQL = strBaseSQL
Set qdf = Nothing
Set rst = Nothing

End Sub
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

If the SQL associated with your query is strBaseSQL, why are you take off
the last 3 characters before appending your WHERE clause?
 
S

Sylvain Lafontaine

Oh, I'm sorry for my mistake. I so accustomed to see Access queries with
double quotes that I forgot to make the must elementary verification before
replying but this is this blank space that caught my attention first...
 
D

Dirk Goldgar

Sylvain Lafontaine said:
Oh, I'm sorry for my mistake. I so accustomed to see Access queries
with double quotes that I forgot to make the must elementary
verification before replying but this is this blank space that caught
my attention first...
Click to expand...

It could still be the case that the value of [Expr1], being concatenated
into the string, contains a single-quote character. That would mess up
the parsing, as you suggested.
 
V

Van T. Dinh

It sounds to me the constructed SQL String is invalid.

Add the statement:

Debug.Print strSQL

just before the statement

qdf.SQL = strSQL

Run the code and you should see the constructed SQL String in the Immediate
window. Copy and post it to the newsgroup.

I agreed with Douglas about removing the last 3 characters from the source
SQL. IIRC, I think you need to remove the last character (;) only, not 3.
 
D

Dirk Goldgar

Douglas J. Steele said:
If the SQL associated with your query is strBaseSQL, why are you take
off the last 3 characters before appending your WHERE clause?
Click to expand...

The SQL may end in a CrLf combination. It does in my tests.
 
V

Van T. Dinh

Hi Dirk

I thought the same as Douglas but I found this bit in one of my databases:

strSQL = Left(strSQL, InStr(1, strSQL, ";") - 1) & ...

so it looks like I did encounter the Cr + Lf in this case also.

I would suggest the InStr like above is probably the safer method than
the -3. It may even be better to use InStrRev in case ";" exists in the SQL
String, e.g. in quote-delimited String embeded in the SQL.

Cheers
Van T. Dinh
 
G

Guest

I actually got the code to get past this error. I am now getting a new error
of Error 3061 Too few parameters. I do have a file date on a form that the
specified query uses. The form is open and populated when I run the macro.
The code errors on the following line:
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
I think my head is going to explode from all of this code.
Thank you all so much for your help. All of you are extremely helpful.

Van T. Dinh said:
It sounds to me the constructed SQL String is invalid.

Add the statement:

Debug.Print strSQL

just before the statement

qdf.SQL = strSQL

Run the code and you should see the constructed SQL String in the Immediate
window. Copy and post it to the newsgroup.

I agreed with Douglas about removing the last 3 characters from the source
SQL. IIRC, I think you need to remove the last character (;) only, not 3.

--
HTH
Van T. Dinh
MVP (Access)




shoe said:
I have the following code which is for looping through each department
(Approximately 200) and sending a separate e-mail to the recipient listed in
a report. I am getting an error 3075( Missing operator) on line qdf.SQL =
strSQL and can't figure out the issue. VBA newbie still trying to figure out
how to create bug free code. Thanks in advance for any assistance you can
offer.

Private Sub cmdEmailReports_Click()
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strBaseSQL As String
Set qdf = CurrentDb.QueryDefs("qryAGHSvcCode")
strBaseSQL = qdf.SQL
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
With rst
Do Until .EOF
strSQL = Left(strBaseSQL, Len(strBaseSQL) - 3)
strSQL = strSQL & " WHERE [Expr1]='" & ![Expr1] & " '"
qdf.SQL = strSQL
DoCmd.SendObject acSendReport, "rptAGHLateCharges",
"SnapshotFormat(*.snp)", !Recipient, , , "YourSubject", "YourMessage", False
.MoveNext
Loop
.Close
End With
qdf.SQL = strBaseSQL
Set qdf = Nothing
Set rst = Nothing

End Sub
Click to expand...
Click to expand...
 
D

Douglas J Steele

So what? Access ignores white space. I always put Cr/Lf in the queries that
I build up from code.
 
D

Douglas J Steele

What does strBaseSQL look like?

I don't understand, though, how you could have gotten past the other
problem. This problem would occur before the other one!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


shoe said:
I actually got the code to get past this error. I am now getting a new error
of Error 3061 Too few parameters. I do have a file date on a form that the
specified query uses. The form is open and populated when I run the macro.
The code errors on the following line:
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
I think my head is going to explode from all of this code.
Thank you all so much for your help. All of you are extremely helpful.

Van T. Dinh said:
It sounds to me the constructed SQL String is invalid.

Add the statement:

Debug.Print strSQL

just before the statement

qdf.SQL = strSQL

Run the code and you should see the constructed SQL String in the Immediate
window. Copy and post it to the newsgroup.

I agreed with Douglas about removing the last 3 characters from the source
SQL. IIRC, I think you need to remove the last character (;) only, not 3.

--
HTH
Van T. Dinh
MVP (Access)




shoe said:
I have the following code which is for looping through each department
(Approximately 200) and sending a separate e-mail to the recipient
Click to expand...
listed
in
a report. I am getting an error 3075( Missing operator) on line qdf.SQL =
strSQL and can't figure out the issue. VBA newbie still trying to
Click to expand...
figure
out
how to create bug free code. Thanks in advance for any assistance you can
offer.

Private Sub cmdEmailReports_Click()
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strBaseSQL As String
Set qdf = CurrentDb.QueryDefs("qryAGHSvcCode")
strBaseSQL = qdf.SQL
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
With rst
Do Until .EOF
strSQL = Left(strBaseSQL, Len(strBaseSQL) - 3)
strSQL = strSQL & " WHERE [Expr1]='" & ![Expr1] & " '"
qdf.SQL = strSQL
DoCmd.SendObject acSendReport, "rptAGHLateCharges",
"SnapshotFormat(*.snp)", !Recipient, , , "YourSubject", "YourMessage", False
.MoveNext
Loop
.Close
End With
qdf.SQL = strBaseSQL
Set qdf = Nothing
Set rst = Nothing

End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

strBaseSQL reads the following:

SELECT [tblImportAGH].[SVCCODE], Left([SVCCODE],3) AS Expr1,
[tblImportAGH].[ACCT NUMBER], [tblImportAGH].[PT NAME], [tblImportAGH].[DOS],
[tblImportAGH].[DTL CR DATE], [tblImportAGH].[POST DATE],
[tblImportAGH].[AMOUNT], [tblImportAGH].[FILE DATE], [tblImportAGH].[InOut],
[POST DATE]-[DOS] AS Days
FROM tblImportAGH
WHERE ((([tblImportAGH].[FILE DATE])=#4/30/2005#)) And [Expr1]='510';

I did figure out the last part because I looked at the syntax and the last
line of the SQL code was reading:
WHERE ((([tblImportAGH].[FILE DATE])=#4/30/2005#)) WHERE [Expr1]='510';

I changed it to input the AND on [Expr1] instead of the second WHERE. I
hope that answers everything you needed. Thanks.

Douglas J Steele said:
What does strBaseSQL look like?

I don't understand, though, how you could have gotten past the other
problem. This problem would occur before the other one!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


shoe said:
I actually got the code to get past this error. I am now getting a new error
of Error 3061 Too few parameters. I do have a file date on a form that the
specified query uses. The form is open and populated when I run the macro.
The code errors on the following line:
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
I think my head is going to explode from all of this code.
Thank you all so much for your help. All of you are extremely helpful.

Van T. Dinh said:
It sounds to me the constructed SQL String is invalid.

Add the statement:

Debug.Print strSQL

just before the statement

qdf.SQL = strSQL

Run the code and you should see the constructed SQL String in the Immediate
window. Copy and post it to the newsgroup.

I agreed with Douglas about removing the last 3 characters from the source
SQL. IIRC, I think you need to remove the last character (;) only, not 3.

--
HTH
Van T. Dinh
MVP (Access)




I have the following code which is for looping through each department
(Approximately 200) and sending a separate e-mail to the recipient listed
in
a report. I am getting an error 3075( Missing operator) on line qdf.SQL =
strSQL and can't figure out the issue. VBA newbie still trying to figure
out
how to create bug free code. Thanks in advance for any assistance you can
offer.

Private Sub cmdEmailReports_Click()
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strBaseSQL As String
Set qdf = CurrentDb.QueryDefs("qryAGHSvcCode")
strBaseSQL = qdf.SQL
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
With rst
Do Until .EOF
strSQL = Left(strBaseSQL, Len(strBaseSQL) - 3)
strSQL = strSQL & " WHERE [Expr1]='" & ![Expr1] & " '"
qdf.SQL = strSQL
DoCmd.SendObject acSendReport, "rptAGHLateCharges",
"SnapshotFormat(*.snp)", !Recipient, , , "YourSubject", "YourMessage",
False
.MoveNext
Loop
.Close
End With
qdf.SQL = strBaseSQL
Set qdf = Nothing
Set rst = Nothing

End Sub
Click to expand...
Click to expand...
Click to expand...
 
D

Dirk Goldgar

Access ignores white space, but if you're going to trim off the closing
";", you'll find it third from the end of the string, not at the end..
 
D

Douglas J Steele

Since Expr1 is a computed field in the query, you can't use it in the WHERE
clause like that.

Instead, you need

And Left([SVCCODE], 3) = '510'

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


shoe said:
strBaseSQL reads the following:

SELECT [tblImportAGH].[SVCCODE], Left([SVCCODE],3) AS Expr1,
[tblImportAGH].[ACCT NUMBER], [tblImportAGH].[PT NAME], [tblImportAGH].[DOS],
[tblImportAGH].[DTL CR DATE], [tblImportAGH].[POST DATE],
[tblImportAGH].[AMOUNT], [tblImportAGH].[FILE DATE], [tblImportAGH].[InOut],
[POST DATE]-[DOS] AS Days
FROM tblImportAGH
WHERE ((([tblImportAGH].[FILE DATE])=#4/30/2005#)) And [Expr1]='510';

I did figure out the last part because I looked at the syntax and the last
line of the SQL code was reading:
WHERE ((([tblImportAGH].[FILE DATE])=#4/30/2005#)) WHERE [Expr1]='510';

I changed it to input the AND on [Expr1] instead of the second WHERE. I
hope that answers everything you needed. Thanks.

Douglas J Steele said:
What does strBaseSQL look like?

I don't understand, though, how you could have gotten past the other
problem. This problem would occur before the other one!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


shoe said:
I actually got the code to get past this error. I am now getting a
Click to expand...
new
error
of Error 3061 Too few parameters. I do have a file date on a form
Click to expand...
that
the
specified query uses. The form is open and populated when I run the macro.
The code errors on the following line:
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
I think my head is going to explode from all of this code.
Thank you all so much for your help. All of you are extremely helpful.

:

It sounds to me the constructed SQL String is invalid.

Add the statement:

Debug.Print strSQL

just before the statement

qdf.SQL = strSQL

Run the code and you should see the constructed SQL String in the Immediate
window. Copy and post it to the newsgroup.

I agreed with Douglas about removing the last 3 characters from the source
SQL. IIRC, I think you need to remove the last character (;) only,
Click to expand...
not
3.
--
HTH
Van T. Dinh
MVP (Access)




I have the following code which is for looping through each department
(Approximately 200) and sending a separate e-mail to the recipient listed
in
a report. I am getting an error 3075( Missing operator) on line qdf.SQL =
strSQL and can't figure out the issue. VBA newbie still trying to figure
out
how to create bug free code. Thanks in advance for any assistance
Click to expand...
you
can
offer.

Private Sub cmdEmailReports_Click()
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strBaseSQL As String
Set qdf = CurrentDb.QueryDefs("qryAGHSvcCode")
strBaseSQL = qdf.SQL
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
With rst
Do Until .EOF
strSQL = Left(strBaseSQL, Len(strBaseSQL) - 3)
strSQL = strSQL & " WHERE [Expr1]='" & ![Expr1] & " '"
qdf.SQL = strSQL
DoCmd.SendObject acSendReport, "rptAGHLateCharges",
"SnapshotFormat(*.snp)", !Recipient, , , "YourSubject", "YourMessage",
False
.MoveNext
Loop
.Close
End With
qdf.SQL = strBaseSQL
Set qdf = Nothing
Set rst = Nothing

End Sub
Click to expand...
Click to expand...
Click to expand...
 
V

Van T. Dinh

Hi Doug

I think the main purpose is to remove the semi-colon at the end of SQL
String. Since the O.P. used Left(), he needs to remove the last 3
characters ( ; + Cr + Lf).

I found a piece of old code I used to trucate the SQL String at the
semi-colon which can cater for Cr + Lf after the semi-colon if they are
there (posted in the other sub-thread).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Export data to multiple excel spreadsheets 2
Code that worked ?! 1
Send an Query output with a Email using "sendObject" function 1
Error: Too few paramenters 7
Exporting multiple Excel spreadhsheets using Excel templates 7
Help with my code Exporting to multiple excel spreadsheets 3
VBA Code Help 3
Changing Report Dynamically 2

Top