Missing semicolon error???

A

Al

I am trying to run an Insert query and keep getting a "Rub-time error '3137':
Missing semicolon (;) at end of SQL stement. I haven not seen this error
before; however, I have a form event that triggers the following code to run
(I populate the variables and have other code too, but this is where the
problem lies):

Dim strSQL1 As String
Dim objDB As DAO.Database
Dim objRS As DAO.Recordset

strSQL1 = "INSERT INTO StatusArchive([ID],[Priority],[Prev Activity],[Curr
Activity],[Plan
Activity],[Phase],[Percent],[BeginDate],
Code: 
,[Rank],[Scolor]) Values ( " &
PID & ",'" + CStr(Prty) + "','" + CStr(PrAct) + "','" + CStr(cAct) + "','" +
CStr(PlAct) + "','" + CStr(Phas) + "'," + Pcent + ",'" + CStr(FridayOfWeek) +
"','" + CStr(Cd) + "'," & Rnk & "," & sCol & ") Where [ID]  = " & PID & ""

Set objDB = CurrentDb
Set objRS = objDB.OpenRecordset(strSQL1)   'IT ERRORS HERE

Call objRS.Close
Set objRS = Nothing
Set objDB = Nothing

This is what I see in debug (everything is getting populated fine):
"INSERT INTO StatusArchive([ID],[Priority],[Prev Activity],[Curr
Activity],[Plan
Activity],[Phase],[Percent],[BeginDate],[Code],[Rank],[Scolor]) Values (
1,'High','','Test Curr','Test Plan','Complete',1,'Friday, September 12,
2008','C',1,1) Where [ID]  = 1"

Thanks in advance,
Al
 
R

Rob Parker

Hi Al,

The error message is telling you that you need to terminate the SQL
statement with a semi-colon. Your code that sets strSQL1 does not do so -
and the output from the debug confirms this. All you should need to do is
amend the final "" in the statement that sets strSQL1 to ";".

strSQL1 = "INSERT INTO StatusArchive([ID],[Priority],[Prev Activity],[Curr
Activity],[Plan
Activity],[Phase],[Percent],[BeginDate],
Code: 
,[Rank],[Scolor]) Values ( " &
PID & ",'" + CStr(Prty) + "','" + CStr(PrAct) + "','" + CStr(cAct) + "','" +
CStr(PlAct) + "','" + CStr(Phas) + "'," + Pcent + ",'" + CStr(FridayOfWeek)
+ "','" + CStr(Cd) + "'," & Rnk & "," & sCol & ") Where [ID]  = " & PID &
";"

Note also that if [BeginDate] is a DateTime field, then it needs #
delimiters rather than ' delimiters, and it won't accept a text string such
as the one returned by your FridayOfWeek function.  Further, I'd suggest
that if, as the field name implies, it is storing date data as text, then
you should change the field type.  You can always display DateTime data in a
string form, using a long date format, etc; however, for comparisons (eg.
criteria in queries) you will be making a rod for your own back if you store
dates as text.

HTH,

Rob
 
D

Dale Fye

Actually, I think your problem is that you are trying to open an action
query as a recordset. Unless this is actually a pass-thru query to SQL
Server, I don't think you really need the terminal semi-colon.

Replace:

set objRS = objDB.OpenRecordset(strSQL1)

with:

objDB.execute strSQL1

then delete the RS.Close and RS = Nothing statements.

HTH
Dale
 
D

david

Access seldom if ever requires a semicolon. What the message tells you
is that there is ambiguous code after the end of the SQL. In this case,
it tells you that Access prefers to have (brackets) around every clause,
that Access has found "=1" after the end of the query, which ended with
Where [ID]
and that you can fix the problem either by using a semicolon to
unambiqously indicate the end, or (brackets) to group the criteria

Where ( [ID] = 1 )

or both:
Where ( [ID] = 1 );


(david)
 
B

bcap

This (misleading) error message occurs because a WHERE clause is meaningless
and invalid in an INSERT statement.

Mind you, having removed the WHERE clause you will next hit the problem
highlighted by Dale Fye, which is that you are trying to open a recordset
using an action query. Dale's solution is almost there, you should do this:

objDB.execute strSQL1, dbFailOnError
 
D

Dale Fye

Agreed, but if you are going to add the dbFailOnError option, youalso need
to write an error handling routine.

Just to expound on bcap's comment. If you leave the dbFailOnError off of
the Execute method, and the insert fails, you will not receive any type of
error message. So, if you try to insert a record that will violate a unique
constraint or which has the wrong datatype assigned to a field, the insert
will not occur, and you won't get a warning message.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



bcap said:
This (misleading) error message occurs because a WHERE clause is meaningless
and invalid in an INSERT statement.

Mind you, having removed the WHERE clause you will next hit the problem
highlighted by Dale Fye, which is that you are trying to open a recordset
using an action query. Dale's solution is almost there, you should do this:

objDB.execute strSQL1, dbFailOnError


Al said:
I am trying to run an Insert query and keep getting a "Rub-time error
'3137':
Missing semicolon (;) at end of SQL stement. I haven not seen this error
before; however, I have a form event that triggers the following code to
run
(I populate the variables and have other code too, but this is where the
problem lies):

Dim strSQL1 As String
Dim objDB As DAO.Database
Dim objRS As DAO.Recordset

strSQL1 = "INSERT INTO StatusArchive([ID],[Priority],[Prev Activity],[Curr
Activity],[Plan
Activity],[Phase],[Percent],[BeginDate],
Code: 
,[Rank],[Scolor]) Values ( "
&
PID & ",'" + CStr(Prty) + "','" + CStr(PrAct) + "','" + CStr(cAct) + "','"
+
CStr(PlAct) + "','" + CStr(Phas) + "'," + Pcent + ",'" +
CStr(FridayOfWeek) +
"','" + CStr(Cd) + "'," & Rnk & "," & sCol & ") Where [ID]  = " & PID & ""

Set objDB = CurrentDb
Set objRS = objDB.OpenRecordset(strSQL1)   'IT ERRORS HERE

Call objRS.Close
Set objRS = Nothing
Set objDB = Nothing

This is what I see in debug (everything is getting populated fine):
"INSERT INTO StatusArchive([ID],[Priority],[Prev Activity],[Curr
Activity],[Plan
Activity],[Phase],[Percent],[BeginDate],[Code],[Rank],[Scolor]) Values (
1,'High','','Test Curr','Test Plan','Complete',1,'Friday, September 12,
2008','C',1,1) Where [ID]  = 1"

Thanks in advance,
Al[/QUOTE]
[/QUOTE]
Click to expand...
Click to expand...
 
A

Al

You were all right on the points you gave me. As the saying goes "It takes a
village..." Thank you all very much for your input; I couldn't have done it
without your help!
Al

bcap said:
This (misleading) error message occurs because a WHERE clause is meaningless
and invalid in an INSERT statement.

Mind you, having removed the WHERE clause you will next hit the problem
highlighted by Dale Fye, which is that you are trying to open a recordset
using an action query. Dale's solution is almost there, you should do this:

objDB.execute strSQL1, dbFailOnError


Al said:
I am trying to run an Insert query and keep getting a "Rub-time error
'3137':
Missing semicolon (;) at end of SQL stement. I haven not seen this error
before; however, I have a form event that triggers the following code to
run
(I populate the variables and have other code too, but this is where the
problem lies):

Dim strSQL1 As String
Dim objDB As DAO.Database
Dim objRS As DAO.Recordset

strSQL1 = "INSERT INTO StatusArchive([ID],[Priority],[Prev Activity],[Curr
Activity],[Plan
Activity],[Phase],[Percent],[BeginDate],
Code: 
,[Rank],[Scolor]) Values ( "
&
PID & ",'" + CStr(Prty) + "','" + CStr(PrAct) + "','" + CStr(cAct) + "','"
+
CStr(PlAct) + "','" + CStr(Phas) + "'," + Pcent + ",'" +
CStr(FridayOfWeek) +
"','" + CStr(Cd) + "'," & Rnk & "," & sCol & ") Where [ID]  = " & PID & ""

Set objDB = CurrentDb
Set objRS = objDB.OpenRecordset(strSQL1)   'IT ERRORS HERE

Call objRS.Close
Set objRS = Nothing
Set objDB = Nothing

This is what I see in debug (everything is getting populated fine):
"INSERT INTO StatusArchive([ID],[Priority],[Prev Activity],[Curr
Activity],[Plan
Activity],[Phase],[Percent],[BeginDate],[Code],[Rank],[Scolor]) Values (
1,'High','','Test Curr','Test Plan','Complete',1,'Friday, September 12,
2008','C',1,1) Where [ID]  = 1"

Thanks in advance,
Al[/QUOTE]
[/QUOTE]
Click to expand...
Click to expand...
 
Top