strSQL Syntax error

[Steven M. Britton]

I'm at a loss as to why this isn't working. I'm building an SQL string from
a dump out of a lotus notes database. I am getting an error message that
says "Syntax error in string in query expression". - My confusion is if I
paste the statement out of the immediate window into a query and hit "!" -
run - it appends the data, but using db.execute theres an error??? Help

Yes, there is a big space between the information.
Code below:
strSQL = "INSERT INTO tblECNs ([Title], [Authors], [Suggester],
[ECN_Number], [Model_Year], [Reason], [Description]) " _
& "VALUES ('" & strECN_Title & "', '" & strAuthors & "', '"
& strSuggester & "', '" & strECN_Number & "', '" & strModel_Year & "', '" &
strReason & "', '" & strDescription & "');"
db.Execute strSQL


INSERT INTO tblECNs ([Title], [Authors], [Suggester], [ECN_Number],
[Model_Year], [Reason], [Description]) VALUES ('Exhaust Support', 'Ronnie
Gonzales', 'Steve Parker Mercury Marine', '9802', '2004', 'Quality', 'To
prevent the intrusion of water in the Gasoline engines a exhaust mount board
will be added to the Bill of Materials and Documentation. This will aid in
the proper flow and prevent the exhaust from sagging. This will add 3.6 ft of
seaboard to the bom. 2 ea. of P/N 1701887 @ 40" in length will be deleted
from the BOM and P/N 127681 muffler mount boards will also be deleted.

C 200 Plastic Sheet, HDPE 3/4" x 3/4"
9.180');

 

[Brendan Reynolds]

The quotes in the Description are the problem. Try this ...

strReason & "', '" & Replace(strDescription, """", """""") & "');"

That's four double quotes in the second argument to the Replace function,
and six double quotes in the third argument. If you prefer, you can use the
Chr$() function, which you may find more readable ...

strReason & "', '" & Replace(strDescription, Chr$(34), Chr$(34) & Chr$(34))
& "');"

 

[Douglas J Steele]

You sure, Brendan?

He's using single quotes to delimit, and I don't see any single quotes in
the Comment.

It might be worth using

db.Execute strSQL, dbFailOnError

and see what error is raised.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The quotes in the Description are the problem. Try this ...

strReason & "', '" & Replace(strDescription, """", """""") & "');"

That's four double quotes in the second argument to the Replace function,
and six double quotes in the third argument. If you prefer, you can use the
Chr$() function, which you may find more readable ...

strReason & "', '" & Replace(strDescription, Chr$(34), Chr$(34) & Chr$(34))
& "');"

--
Brendan Reynolds
Access MVP

I'm at a loss as to why this isn't working. I'm building an SQL string
from
a dump out of a lotus notes database. I am getting an error message that
says "Syntax error in string in query expression". - My confusion is if I
paste the statement out of the immediate window into a query and hit "!" -
run - it appends the data, but using db.execute theres an error??? Help

Yes, there is a big space between the information.
Code below:
strSQL = "INSERT INTO tblECNs ([Title], [Authors], [Suggester],
[ECN_Number], [Model_Year], [Reason], [Description]) " _
& "VALUES ('" & strECN_Title & "', '" & strAuthors & "', '"
& strSuggester & "', '" & strECN_Number & "', '" & strModel_Year & "', '"
&
strReason & "', '" & strDescription & "');"
db.Execute strSQL


INSERT INTO tblECNs ([Title], [Authors], [Suggester], [ECN_Number],
[Model_Year], [Reason], [Description]) VALUES ('Exhaust Support', 'Ronnie
Gonzales', 'Steve Parker Mercury Marine', '9802', '2004', 'Quality', 'To
prevent the intrusion of water in the Gasoline engines a exhaust mount
board
will be added to the Bill of Materials and Documentation. This will aid in
the proper flow and prevent the exhaust from sagging. This will add 3.6 ft
of
seaboard to the bom. 2 ea. of P/N 1701887 @ 40" in length will be deleted
from the BOM and P/N 127681 muffler mount boards will also be deleted.

C 200 Plastic Sheet, HDPE 3/4" x
3/4"
9.180');

 

[Steven M. Britton]

Doug,

I am of the same opinion as you using the singel quote "should" make it so I
didn't have to deal with quotes in the description. I did as you suggested
and received the same error AND I did and Brendan suggested and still
received the same error. As I typing this I think it has something to do
with the pseudo character returns in the text field from the lotus dump.

This is how it looks on the lotus form:
To prevent the intrusion of water in the Gasoline engines a exhaust mount
board will be added to the Bill of Materials and Documentation. This will aid
in the proper flow and prevent the exhaust from sagging. This will add 3.6 ft
of seaboard to the bom. 2 ea. of P/N 1701887 @ 40" in length will be deleted
from the BOM and P/N 127681 muffler mount boards will also be deleted.

Current Bom Quantity KIT 221 PSC 200 Plastic Sheet, HDPE 3/4" x 3/4"
5.580

New Bom Quantity KIT 221 PSC 200 Plastic Sheet, HDPE 3/4" x 3/4"
9.180

But when I use the

Set fso = CreateObject("Scripting.FileSystemObject")

With fso
Set fIn = .OpenTextFile(FileSpec, ForReading)

strLine = fIn.ReadLine

Somehow is reads the returns "weird"... I'm going to try somethings here
and either of you have an idea let me know. I'll post if I get anywhere.

You sure, Brendan?

He's using single quotes to delimit, and I don't see any single quotes in
the Comment.

It might be worth using

db.Execute strSQL, dbFailOnError

and see what error is raised.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The quotes in the Description are the problem. Try this ...

strReason & "', '" & Replace(strDescription, """", """""") & "');"

That's four double quotes in the second argument to the Replace function,
and six double quotes in the third argument. If you prefer, you can use the
Chr$() function, which you may find more readable ...

strReason & "', '" & Replace(strDescription, Chr$(34), Chr$(34) & Chr$(34))
& "');"

--
Brendan Reynolds
Access MVP

I'm at a loss as to why this isn't working. I'm building an SQL string
from
a dump out of a lotus notes database. I am getting an error message that
says "Syntax error in string in query expression". - My confusion is if I
paste the statement out of the immediate window into a query and hit "!" -
run - it appends the data, but using db.execute theres an error??? Help

Yes, there is a big space between the information.
Code below:
strSQL = "INSERT INTO tblECNs ([Title], [Authors], [Suggester],
[ECN_Number], [Model_Year], [Reason], [Description]) " _
& "VALUES ('" & strECN_Title & "', '" & strAuthors & "', '"
& strSuggester & "', '" & strECN_Number & "', '" & strModel_Year & "', '"
&
strReason & "', '" & strDescription & "');"
db.Execute strSQL


INSERT INTO tblECNs ([Title], [Authors], [Suggester], [ECN_Number],
[Model_Year], [Reason], [Description]) VALUES ('Exhaust Support', 'Ronnie
Gonzales', 'Steve Parker Mercury Marine', '9802', '2004', 'Quality', 'To
prevent the intrusion of water in the Gasoline engines a exhaust mount
board
will be added to the Bill of Materials and Documentation. This will aid in
the proper flow and prevent the exhaust from sagging. This will add 3.6 ft
of
seaboard to the bom. 2 ea. of P/N 1701887 @ 40" in length will be deleted
from the BOM and P/N 127681 muffler mount boards will also be deleted.

C 200 Plastic Sheet, HDPE 3/4" x
3/4"
9.180');

 

[Brendan Reynolds]

I expect you're both right - I jumped too quickly to the conclusion that the
quotes were the problem. I also expect that you're right to suspect the line
endings - perhaps they are single line-feed characters without
carriage-returns, or single carriage-returns without line feeds?

--
Brendan Reynolds
Access MVP

Doug,

I am of the same opinion as you using the singel quote "should" make it so
I
didn't have to deal with quotes in the description. I did as you
suggested
and received the same error AND I did and Brendan suggested and still
received the same error. As I typing this I think it has something to do
with the pseudo character returns in the text field from the lotus dump.

This is how it looks on the lotus form:
To prevent the intrusion of water in the Gasoline engines a exhaust mount
board will be added to the Bill of Materials and Documentation. This will
aid
in the proper flow and prevent the exhaust from sagging. This will add 3.6
ft
of seaboard to the bom. 2 ea. of P/N 1701887 @ 40" in length will be
deleted
from the BOM and P/N 127681 muffler mount boards will also be deleted.

Current Bom Quantity KIT 221 PSC 200 Plastic Sheet, HDPE 3/4" x 3/4"
5.580

New Bom Quantity KIT 221 PSC 200 Plastic Sheet, HDPE 3/4" x 3/4"
9.180

But when I use the

Set fso = CreateObject("Scripting.FileSystemObject")

With fso
Set fIn = .OpenTextFile(FileSpec, ForReading)

strLine = fIn.ReadLine

Somehow is reads the returns "weird"... I'm going to try somethings here
and either of you have an idea let me know. I'll post if I get anywhere.

You sure, Brendan?

He's using single quotes to delimit, and I don't see any single quotes in
the Comment.

It might be worth using

db.Execute strSQL, dbFailOnError

and see what error is raised.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The quotes in the Description are the problem. Try this ...

strReason & "', '" & Replace(strDescription, """", """""") & "');"

That's four double quotes in the second argument to the Replace
function,
and six double quotes in the third argument. If you prefer, you can use the
Chr$() function, which you may find more readable ...

strReason & "', '" & Replace(strDescription, Chr$(34), Chr$(34) & Chr$(34))
& "');"

--
Brendan Reynolds
Access MVP

message I'm at a loss as to why this isn't working. I'm building an SQL
string
from
a dump out of a lotus notes database. I am getting an error message that
says "Syntax error in string in query expression". - My confusion is
if I
paste the statement out of the immediate window into a query and hit "!" -
run - it appends the data, but using db.execute theres an error???
Help

Yes, there is a big space between the information.
Code below:
strSQL = "INSERT INTO tblECNs ([Title], [Authors],
[Suggester],
[ECN_Number], [Model_Year], [Reason], [Description]) " _
& "VALUES ('" & strECN_Title & "', '" & strAuthors &
"', '"
& strSuggester & "', '" & strECN_Number & "', '" & strModel_Year &
"', '"
&
strReason & "', '" & strDescription & "');"
db.Execute strSQL


INSERT INTO tblECNs ([Title], [Authors], [Suggester], [ECN_Number],
[Model_Year], [Reason], [Description]) VALUES ('Exhaust Support', 'Ronnie
Gonzales', 'Steve Parker Mercury Marine', '9802', '2004', 'Quality',
'To
prevent the intrusion of water in the Gasoline engines a exhaust
mount
board
will be added to the Bill of Materials and Documentation. This will
aid in
the proper flow and prevent the exhaust from sagging. This will add
3.6 ft
of
seaboard to the bom. 2 ea. of P/N 1701887 @ 40" in length will be deleted
from the BOM and P/N 127681 muffler mount boards will also be
deleted.

C 200 Plastic Sheet, HDPE 3/4"
x
3/4"
9.180');

 

[Marc Rondeau]

test

I expect you're both right - I jumped too quickly to the conclusion that
the quotes were the problem. I also expect that you're right to suspect
the line endings - perhaps they are single line-feed characters without
carriage-returns, or single carriage-returns without line feeds?

--
Brendan Reynolds
Access MVP

Doug,

I am of the same opinion as you using the singel quote "should" make it
so I
didn't have to deal with quotes in the description. I did as you
suggested
and received the same error AND I did and Brendan suggested and still
received the same error. As I typing this I think it has something to do
with the pseudo character returns in the text field from the lotus dump.

This is how it looks on the lotus form:
To prevent the intrusion of water in the Gasoline engines a exhaust mount
board will be added to the Bill of Materials and Documentation. This will
aid
in the proper flow and prevent the exhaust from sagging. This will add
3.6 ft
of seaboard to the bom. 2 ea. of P/N 1701887 @ 40" in length will be
deleted
from the BOM and P/N 127681 muffler mount boards will also be deleted.

Current Bom Quantity KIT 221 PSC 200 Plastic Sheet, HDPE 3/4" x 3/4"
5.580

New Bom Quantity KIT 221 PSC 200 Plastic Sheet, HDPE 3/4" x 3/4"
9.180

But when I use the

Set fso = CreateObject("Scripting.FileSystemObject")

With fso
Set fIn = .OpenTextFile(FileSpec, ForReading)

strLine = fIn.ReadLine

Somehow is reads the returns "weird"... I'm going to try somethings here
and either of you have an idea let me know. I'll post if I get anywhere.

You sure, Brendan?

He's using single quotes to delimit, and I don't see any single quotes
in
the Comment.

It might be worth using

db.Execute strSQL, dbFailOnError

and see what error is raised.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



The quotes in the Description are the problem. Try this ...

strReason & "', '" & Replace(strDescription, """", """""") & "');"

That's four double quotes in the second argument to the Replace
function,
and six double quotes in the third argument. If you prefer, you can
use
the
Chr$() function, which you may find more readable ...

strReason & "', '" & Replace(strDescription, Chr$(34), Chr$(34) &
Chr$(34))
& "');"

--
Brendan Reynolds
Access MVP

in
message I'm at a loss as to why this isn't working. I'm building an SQL
string
from
a dump out of a lotus notes database. I am getting an error message
that
says "Syntax error in string in query expression". - My confusion
is if
I
paste the statement out of the immediate window into a query and hit
"!" -
run - it appends the data, but using db.execute theres an error???
Help

Yes, there is a big space between the information.
Code below:
strSQL = "INSERT INTO tblECNs ([Title], [Authors],
[Suggester],
[ECN_Number], [Model_Year], [Reason], [Description]) " _
& "VALUES ('" & strECN_Title & "', '" & strAuthors &
"',
'"
& strSuggester & "', '" & strECN_Number & "', '" & strModel_Year &
"',
'"
&
strReason & "', '" & strDescription & "');"
db.Execute strSQL


INSERT INTO tblECNs ([Title], [Authors], [Suggester], [ECN_Number],
[Model_Year], [Reason], [Description]) VALUES ('Exhaust Support',
'Ronnie
Gonzales', 'Steve Parker Mercury Marine', '9802', '2004', 'Quality',
'To
prevent the intrusion of water in the Gasoline engines a exhaust
mount
board
will be added to the Bill of Materials and Documentation. This will
aid
in
the proper flow and prevent the exhaust from sagging. This will add
3.6
ft
of
seaboard to the bom. 2 ea. of P/N 1701887 @ 40" in length will be
deleted
from the BOM and P/N 127681 muffler mount boards will also be
deleted.

C 200 Plastic Sheet, HDPE 3/4"
x
3/4"
9.180');

 

[Pieter Wijnen]

The simple Solution might be to parameterize the statement
Dim Qdef AS DAO.QueryDef
Dim Parm AS DAO.Parameter

Set QDef = Db.CreateQueryDef(VBA.vbNullString)
strSQL = "PARAMETERS pTitle TEXT, pAuthors TEXT ........................;" &
_
"INSERT INTO tblECNs ([Title], [Authors], [Suggester],
[ECN_Number], [Model_Year], [Reason], [Description]) " _
& "VALUES ([pTitle] , [pAuthors] .................)"
..............
Qdef.Parameters("pTitle").Value=strECN_Title
.................
Qdef.Execute DAO.DbSeeChanges
...................

HTH

Pieter

test

I expect you're both right - I jumped too quickly to the conclusion that
the quotes were the problem. I also expect that you're right to suspect
the line endings - perhaps they are single line-feed characters without
carriage-returns, or single carriage-returns without line feeds?

--
Brendan Reynolds
Access MVP

Doug,

I am of the same opinion as you using the singel quote "should" make it
so I
didn't have to deal with quotes in the description. I did as you
suggested
and received the same error AND I did and Brendan suggested and still
received the same error. As I typing this I think it has something to
do
with the pseudo character returns in the text field from the lotus dump.

This is how it looks on the lotus form:
To prevent the intrusion of water in the Gasoline engines a exhaust
mount
board will be added to the Bill of Materials and Documentation. This
will aid
in the proper flow and prevent the exhaust from sagging. This will add
3.6 ft
of seaboard to the bom. 2 ea. of P/N 1701887 @ 40" in length will be
deleted
from the BOM and P/N 127681 muffler mount boards will also be deleted.

Current Bom Quantity KIT 221 PSC 200 Plastic Sheet, HDPE 3/4" x 3/4"
5.580

New Bom Quantity KIT 221 PSC 200 Plastic Sheet, HDPE 3/4" x 3/4"
9.180

But when I use the

Set fso = CreateObject("Scripting.FileSystemObject")

With fso
Set fIn = .OpenTextFile(FileSpec, ForReading)

strLine = fIn.ReadLine

Somehow is reads the returns "weird"... I'm going to try somethings
here
and either of you have an idea let me know. I'll post if I get
anywhere.


:

You sure, Brendan?

He's using single quotes to delimit, and I don't see any single quotes
in
the Comment.

It might be worth using

db.Execute strSQL, dbFailOnError

and see what error is raised.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message

The quotes in the Description are the problem. Try this ...

strReason & "', '" & Replace(strDescription, """", """""") & "');"

That's four double quotes in the second argument to the Replace
function,
and six double quotes in the third argument. If you prefer, you can
use
the
Chr$() function, which you may find more readable ...

strReason & "', '" & Replace(strDescription, Chr$(34), Chr$(34) &
Chr$(34))
& "');"

--
Brendan Reynolds
Access MVP

in
message I'm at a loss as to why this isn't working. I'm building an SQL
string
from
a dump out of a lotus notes database. I am getting an error
message
that
says "Syntax error in string in query expression". - My confusion
is if
I
paste the statement out of the immediate window into a query and
hit
"!" -
run - it appends the data, but using db.execute theres an error???
Help

Yes, there is a big space between the information.
Code below:
strSQL = "INSERT INTO tblECNs ([Title], [Authors],
[Suggester],
[ECN_Number], [Model_Year], [Reason], [Description]) " _
& "VALUES ('" & strECN_Title & "', '" & strAuthors &
"',
'"
& strSuggester & "', '" & strECN_Number & "', '" & strModel_Year &
"',
'"
&
strReason & "', '" & strDescription & "');"
db.Execute strSQL


INSERT INTO tblECNs ([Title], [Authors], [Suggester], [ECN_Number],
[Model_Year], [Reason], [Description]) VALUES ('Exhaust Support',
'Ronnie
Gonzales', 'Steve Parker Mercury Marine', '9802', '2004',
'Quality', 'To
prevent the intrusion of water in the Gasoline engines a exhaust
mount
board
will be added to the Bill of Materials and Documentation. This will
aid
in
the proper flow and prevent the exhaust from sagging. This will add
3.6
ft
of
seaboard to the bom. 2 ea. of P/N 1701887 @ 40" in length will be
deleted
from the BOM and P/N 127681 muffler mount boards will also be
deleted.

C 200 Plastic Sheet, HDPE
3/4" x
3/4"
9.180');