Missing Operator

[Joe Cilinceon]

I trying to execute this inside a letter printing form. It puts a note in
the tenant's notes "Printed: Late Letter for Unit#" (then the unit number0.
Now it works fine in a Query but keeps giving me a syntax error missing
operator. I've tried it a bunch of different ways but can't seem to get what
I'm obviously missing.

Here is the string:

strSQL = "INSERT INTO tblTenantNotes ( CustNo, [Note] )" & _
"SELECT qryBalanceDue.CustNo, 'Printed: Late Letter for Unit #'" &
_
"[Unit] AS [Note] FROM qryBalanceDue" & _
"WHERE (((qryBalanceDue.Days)>9 And (qryBalanceDue.Days)<45));"

 

[Brendan Reynolds]

It looks like you're missing some important spaces, for example ...

"[Unit] AS [Note] FROM qryBalanceDue" & _
"WHERE (((qryBalanceDue.Days)>9 And (qryBalanceDue.Days)<45));"

This will result in a string that contains "qryBalanceDueWHERE" instead of
"qryBalanceDue WHERE"

Trying Debug.Printing the variable to the Immediate window, and the problem
will likely become clear.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

I trying to execute this inside a letter printing form. It puts a note in
the tenant's notes "Printed: Late Letter for Unit#" (then the unit
number0.
Now it works fine in a Query but keeps giving me a syntax error missing
operator. I've tried it a bunch of different ways but can't seem to get
what
I'm obviously missing.

Here is the string:

strSQL = "INSERT INTO tblTenantNotes ( CustNo, [Note] )" & _
"SELECT qryBalanceDue.CustNo, 'Printed: Late Letter for Unit #'" &
_
"[Unit] AS [Note] FROM qryBalanceDue" & _
"WHERE (((qryBalanceDue.Days)>9 And (qryBalanceDue.Days)<45));"

 

[Tom Ellison]

Dear Joe:

You're missing something in the query generation. Put a break point
on the line following the one you showed in your post and display
strSQL in the immediate pane, or use Debug.Print strSQL in the code.
The string you've generated is in error.

When you convert a working SQL string into a code-generated string,
there will commonly be new lines within the SQL String. I recomend
you place a space in the string at this point so the "words" don't run
together and ruin it. Below, I've inserted B for blank where you
should have these spaces. Some of them are critical:

strSQL = "INSERT INTO tblTenantNotes ( CustNo, [Note] )B" & _
"SELECT qryBalanceDue.CustNo, 'Printed: Late Letter for Unit
#'B" &_
"[Unit] AS [Note] FROM qryBalanceDueB" & _
"WHERE (((qryBalanceDue.Days)>9 And
(qryBalanceDue.Days)<45));"

This should help. If you stop and look at the string you generated,
you'll see why leaving these spaces out ruins the code.

I believe that only the last of these I inserted is critical. The
other's are best for style, but might become important if you ever
need to edit it. In any case, they don't hurt, and it's a good
practice to use them.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Joe Cilinceon]

Thanks Tom and Brendan that got it and I see where I missed the spaces.

--

Joe Cilinceon

Dear Joe:

You're missing something in the query generation. Put a break point
on the line following the one you showed in your post and display
strSQL in the immediate pane, or use Debug.Print strSQL in the code.
The string you've generated is in error.

When you convert a working SQL string into a code-generated string,
there will commonly be new lines within the SQL String. I recomend
you place a space in the string at this point so the "words" don't run
together and ruin it. Below, I've inserted B for blank where you
should have these spaces. Some of them are critical:

strSQL = "INSERT INTO tblTenantNotes ( CustNo, [Note] )B" & _
"SELECT qryBalanceDue.CustNo, 'Printed: Late Letter for Unit
#'B" &_
"[Unit] AS [Note] FROM qryBalanceDueB" & _
"WHERE (((qryBalanceDue.Days)>9 And
(qryBalanceDue.Days)<45));"

This should help. If you stop and look at the string you generated,
you'll see why leaving these spaces out ruins the code.

I believe that only the last of these I inserted is critical. The
other's are best for style, but might become important if you ever
need to edit it. In any case, they don't hurt, and it's a good
practice to use them.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

I trying to execute this inside a letter printing form. It puts a note in
the tenant's notes "Printed: Late Letter for Unit#" (then the unit number0.
Now it works fine in a Query but keeps giving me a syntax error missing
operator. I've tried it a bunch of different ways but can't seem to get what
I'm obviously missing.

Here is the string:

strSQL = "INSERT INTO tblTenantNotes ( CustNo, [Note] )" & _
"SELECT qryBalanceDue.CustNo, 'Printed: Late Letter for Unit #'" &
_
"[Unit] AS [Note] FROM qryBalanceDue" & _
"WHERE (((qryBalanceDue.Days)>9 And (qryBalanceDue.Days)<45));"