5 Replies - 231 Views - Last Post: 24 October 2017 - 07:37 AM Rate Topic: -----

#1 96_david_97  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 44
  • Joined: 03-August 16

Syntax Error in SQL Query, but the syntax is fine

Posted 24 October 2017 - 06:57 AM

I've got 2 string variables, SQL and strNominal. The following code is returning an error

   SQL = "SELECT Turnover_PTD FROM [Nominal Ledger] WHERE [Nominal_Code] = '" & strNominal & "'"
   Set rsPeriod = periodDB.OpenRecordSet(SQL, dbOpenDynaset)



I know that it's bad practice to pass in the variable this way however I'm not familiar with VB6, and have never used parameters. I've just been asked to make a quick change to an application written in VB6, so I'm doing it this way for time efficiency. I use parameters .NET, however.

When the code gets to the second line of code, it errors, saying "Syntax error in query expression". I've racked my brain and analysed the string thoroughly and I'm certain there is no syntax error... Am I missing something obvious?

Is This A Good Question/Topic? 0
  • +

Replies To: Syntax Error in SQL Query, but the syntax is fine

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13485
  • View blog
  • Posts: 53,852
  • Joined: 12-June 08

Re: Syntax Error in SQL Query, but the syntax is fine

Posted 24 October 2017 - 07:09 AM

If you put a breakpoint there and examine the actual results in the SQL variable.. and then, say, run them in your DB query window... what happens?
Was This Post Helpful? 0
  • +
  • -

#3 96_david_97  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 44
  • Joined: 03-August 16

Re: Syntax Error in SQL Query, but the syntax is fine

Posted 24 October 2017 - 07:18 AM

View Postmodi123_1, on 24 October 2017 - 07:09 AM, said:

If you put a breakpoint there and examine the actual results in the SQL variable.. and then, say, run them in your DB query window... what happens?


A message is popped... "The SELECT statement includes a reserved word or an argument that is misspelled or missing, or the punctuation is incorrect"

It highlights the "urno" in my Turnover_PTD field name. Not sure this is actually relevant though because I know this is spelled correctly. It even does it if I copy the field name from Design view and use that in the query instead.
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13485
  • View blog
  • Posts: 53,852
  • Joined: 12-June 08

Re: Syntax Error in SQL Query, but the syntax is fine

Posted 24 October 2017 - 07:26 AM

Quote

It highlights the "urno" in my Turnover_PTD field name.

What?
Was This Post Helpful? 0
  • +
  • -

#5 96_david_97  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 44
  • Joined: 03-August 16

Re: Syntax Error in SQL Query, but the syntax is fine

Posted 24 October 2017 - 07:33 AM

@modi123_1 ignore that part. The message it displays when running the query in Access is the one I posted. The bit about highlighting isn't relevant at all.
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13485
  • View blog
  • Posts: 53,852
  • Joined: 12-June 08

Re: Syntax Error in SQL Query, but the syntax is fine

Posted 24 October 2017 - 07:37 AM

Ok. Again you need to do some debugging. I am not there to do the actual manipulation.

What happens when you take the entire contents of the SQL variable - after it is assigned - and run it?
Are you certain you are not using reserved words?
Have a funky thing inside 'strnominal' where it would screw up your concantonanted sql query?
Have column names, table names, etc right?
.. etc.

There's not enough information given so far to replicate or see.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1