Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Syntax Error in Query Expression

Posted on 2011-09-03
7
Medium Priority
?
384 Views
Last Modified: 2012-05-12
Ok, experts, the below query runs my logging routine for error handling.
On some errors, it works fine. On other, it crashes with a 3075. I cannot see anything wrong with the query syntax! JP-TechGroup-495674.flv
strSQL = "INSERT INTO tbl_log ( err_num, err_descrip, err_source, err_module, [timestamp], terminal, t_data, on_statement, [user], signed_on, [language], state, ct2offline)"
strSQL = strSQL & "Values(" & err.Number & ", '" & err.Description & "', '" & err.Source & "', '"
strSQL = strSQL & VBE.ActiveCodePane.CodeModule & "', #" & Now() & "#, " & terminal & ", '" & data & "', "
strSQL = strSQL & TStatus(terminal).onStatement & ", '" & TStatus(terminal).user & "', " & TStatus(terminal).SignedIn & ", "
strSQL = strSQL & TStatus(terminal).Lang & ", '" & State & "', " & CT2offline & ");"

Open in new window

0
Comment
Question by:JP_TechGroup
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 48

Expert Comment

by:Dale Fye
ID: 36478232
do you have an error handler?

How are you running this action query?  If you are using:

currentdb.execute strSQl, dbfailonerror

Then add a line to your error handler (within your error log routine) that will print out the SQL string.

Is [User] a required field in your table?  In the SQL string, it looks like that is an empty string.
0
 
LVL 35

Expert Comment

by:Norie
ID: 36478246
Where does the last line in the error message come from?

It looks like a repeat of the previous line.

In fact it seems like the SQL statement has been terminated with ; but then something has been added on.
0
 
LVL 1

Author Comment

by:JP_TechGroup
ID: 36478282
The whole things is bizzare.
This is part of the error handler. It writes the error to a log file and then sends the user to a message stating the problem. I am using currentdb.execute

Depending on when the error occurs, there may not be a user, so sometimes there is data missing, which has never been a problem. On that note, I have seen this query fail like this when all the fields are populated.

Here is where it gets odder still. Below is a debug print of the string when it fails. It looks like the query is running 3 times.


INSERT INTO tbl_log ( err_num, err_descrip, err_source, err_module, [timestamp], terminal, t_data, on_statement, [user], signed_on, [language], state, ct2offline)Values(2482, 'C-Tracker v2.0.3 can't find the name 'USER' you entered in the expression.', 'Project', 'Module1', #9/3/2011 11:51:05 AM#, 10, '', 3, '', True, 1, '', False);
INSERT INTO tbl_log ( err_num, err_descrip, err_source, err_module, [timestamp], terminal, t_data, on_statement, [user], signed_on, [language], state, ct2offline)Values(3075, 'Syntax error (missing operator) in query expression ''C-Tracker v2.0.3 can't find the name 'USER' you entered in the expression.', 'Project', 'Module1', #9/3/2011 11:51:05 AM#, 10, '', 3, '', True, 1, '', False);'.', 'DAO.Database', 'Module1', #9/3/2011 11:51:05 AM#, 10, '1', 3, '', True, 1, '', False);
INSERT INTO tbl_log ( err_num, err_descrip, err_source, err_module, [timestamp], terminal, t_data, on_statement, [user], signed_on, [language], state, ct2offline)Values(3075, 'Syntax error (missing operator) in query expression ''Syntax error (missing operator) in query expression ''C-Tracker v2.0.3 can't find the name 'USER' you entered in the expression.', 'Project', 'Module1', #9/3/2011 11:51:05 AM#, 10, '', 3, '', True, 1, '', False);'.', 'DAO.Database', 'Module1', #9/3/2011 11:51:05 AM#, 10, '1', 3, '', True, 1, '', False);'.', 'DAO.Database', 'Module1', #9/3/2011 11:51:05 AM#, 10, '1', 3, '', True, 1, '', False);

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Author Comment

by:JP_TechGroup
ID: 36478285
Here is an example of the same query working correctly on a debug.print.
INSERT INTO tbl_log ( err_num, err_descrip, err_source, err_module, [timestamp], terminal, t_data, on_statement, [user], signed_on, [language], state, ct2offline)Values(11, 'Division by zero', 'Project', 'Module1', #9/3/2011 11:53:12 AM#, 0, '', 0, '', False, 0, '', False);

Open in new window

0
 
LVL 61

Accepted Solution

by:
mbizup earned 1000 total points
ID: 36478339
Try revising line 4 in your orriginal post.

You currently have this:

<<strSQL = strSQL & TStatus(terminal).onStatement & ", '" & TStatus(terminal).user & "', " & TStatus(terminal).SignedIn & ", " >>


Change the User section to this:

strSQL = strSQL & TStatus(terminal).onStatement & ", " & chr(34) & TStatus(terminal).user & chr(34) & ", " & TStatus(terminal).SignedIn & ", "

This uses chr(34) which is double quotes instead of single quotes (') to delimit your usernames.  If you have Irish usernames like O'Brien or O'Malley, using single quotes as in your original post *will* cause your query to fail with this error.

0
 
LVL 10

Assisted Solution

by:plummet
plummet earned 1000 total points
ID: 36478373
As mbizup says, your problem is because you are trying to insert a string that contains a single quote, which will of course confuse the hell out of it.

You need to check that strings don't contain a single quote and if they do I'd suggest replace the single with two single quotes, ie:

use the replace function to replace a single quote "'" with two "''". I realise that may look terrible on screen here so maybe copy it to Access and see what I mean. The function should be like this:

replace(err.Description,"'","''")

Open in new window


so in place in your SQL build:

strSQL = "INSERT INTO tbl_log ( err_num, err_descrip, err_source, err_module, [timestamp], terminal, t_data, on_statement, [user], signed_on, [language], state, ct2offline)"
strSQL = strSQL & "Values(" & err.Number & ", '" & replace(err.Description,"'","''") & "', '" & err.Source & "', '"
strSQL = strSQL & VBE.ActiveCodePane.CodeModule & "', #" & Now() & "#, " & terminal & ", '" & data & "', "
strSQL = strSQL & TStatus(terminal).onStatement & ", '" & TStatus(terminal).user & "', " & TStatus(terminal).SignedIn & ", "
strSQL = strSQL & TStatus(terminal).Lang & ", '" & State & "', " & CT2offline & ");"

Open in new window


I think you should also do the replace on other fields, such as user, just in case. It should do the trick!
0
 
LVL 1

Author Closing Comment

by:JP_TechGroup
ID: 36478405
That did the trick and explains why this error kept tanking the system. This has been driving me nuts! Thank you.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question