×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Error in Select query

Error in Select query

Error in Select query

(OP)
Errormessage :
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in query expression '2023_TafelUni.Tafels'.

CODE -->

sql = "SELECT 2023_SV999_od.*, 2023_TafelUni.Tafels, "&_
"(Select Sum(Number) FROM 2023_SV999_od) AS TotNumber, "&_
"(Select sum(Amount) FROM 2023_SV999_od) AS TotAmount, "&_    
"(Select Sum(Number) FROM 2023_SV999_od WHERE tafel=2023_TafelUni.Tafels) AS TNumber "&_ 
"FROM 2023_TafelUni "&_
"left JOIN 2023_SV999_od ON 2023_TafelUni.Tafels=2023_SV999_od.Tafel "&_
"ORDER BY 2023_TableUni.Tafels, 2023_SV999_od.Clb, 2023_SV999_od.name ASC" 

Database = Access
file 1: 2023_SV9999_od / fields = tafel (numeric), name, (and other)
file 2: 2023_TafelUni / 1 field = Tafels (numeric)

Thanks for tips.

RE: Error in Select query

You have 2023_TableUni rather than 2023_TafelUni in the ORDER BY clause.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Error in Select query

(OP)
Thank you SkipVought for your response.

CODE -->

"ORDER BY 2023_TafelUni.Tafels, 2023_SV999_od.Clb, 2023_SV999_od.name ASC" 

After this "ORDER" adjustment I still get the same error message.
I don't understand why this is a syntax problem.
Where else can I search?
Thanks for tips.

RE: Error in Select query

Do your SQL tables already exist?
I'm curious, because table names shouldn't start with a number.
I tried it in database I'm using and it doesn't work.
This could be the cause of your syntax problem.

RE: Error in Select query

You can most definitely have a table that starts with a number, however the actual SQL query treats it differently.

I created a table called 1A1 and entered several addresses in it. This query returns an error:

select * from 1A1
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '1'.

This query executes w/o error:
select * from [1A1]

So I would bracket your WHERE clause:

CODE

"ORDER BY 2023_TafelUni.Tafels, [2023_SV999_od.Clb], [2023_SV999_od.name] ASC" 

Macola and SAP Business One Consultant
Check out our Macola tools:
www.gainfocus.biz/exceladdin.html

RE: Error in Select query

I would start simple, making sure the SQL works, and than add to it.

Something like:
sql = "SELECT SV.*, TU.Tafels, " & _
  "(Select Sum(Number) FROM 2023_SV999_od) AS TotNumber, " & _
  "(Select sum(Amount) FROM 2023_SV999_od) AS TotAmount, " & _    
  "(Select Sum(Number) FROM 2023_SV999_od WHERE tafel = TU.Tafels) AS TNumber " & _ 
"FROM 2023_TafelUni TU " & _
  "left JOIN 2023_SV999_od ON TU.Tafels = SV.Tafel " & _
"ORDER BY TU.Tafels, SV.Clb, SV.name ASC" 
 

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Error in Select query

...
"FROM 2023_TafelUni TU " & _
"left JOIN 2023_SV999_od SV ON TU.Tafels = SV.Tafel " & _
"ORDER BY TU.Tafels, SV.Clb, SV.name ASC"

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Error in Select query

Normally, before I write a SQL query into a string to use it in a program, I always test it with an interactive SQL client to see if it even works. I doubt the OP tested the query at this way, otherwise this question probably wouldn't be asked at all.

I tested it, I first tried to create the tables 2023_TafelUni and 2023_SV999_od, but it didn't work due to the wrong naming convention. I then created the tables with the correct naming convention i.e. TafelUni_2023 and SV999_2023_od and this query worked:

CODE

select 
  SV999_2023_od.*, TafelUni_2023.Tafels,
  (select Sum(Number) from SV999_2023_od) as TotNumber,
  (select Sum(Amount) from SV999_2023_od) as TotAmount,
  (select Sum(Number) from SV999_2023_od where tafel=TafelUni_2023.tafels) as TNumber
from 
  TafelUni_2023
left join
  SV999_2023_od
on
  TafelUni_2023.Tafels = SV999_2023_od.Tafel
order by 
  TafelUni_2023.Tafels, SV999_2023_od.Clb, SV999_2023_od.name asc 

RE: Error in Select query

I also suspect we have 2023_SV999_od and 2023_TafelUni tables because of AD 2023, and next year leifoet will have 2024_SV999_od and 2024_TafelUni tables, and so on every year... Bad design sad

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Error in Select query

Guys, asssume the OP is innocent in the database design, that's maybe done by an application. And likely the original Access programmer uses the square brackets name convention for tables.

But then, as on site admin, you have the ufortunate task to make some queries on such a construct. Yes, it's helpful to know such names are possible but only using the brackets. In other databases like MySQL you'd need backticks. There's no industry standard, unfortunately.

Then, there were further errors, that would perhaps be found once you know about the name restrictions.

Bad style indeed. Next year you'll need to update all yor queries...Or use a generative approach.

Chriss

RE: Error in Select query

Quote (Chriss)

Next year you'll need to update all yor queries...Or use a generative approach
I would hate to maintain code like:

strSQL = "select " & _
" SV999_" & Year(Date()) & "_od.*, TafelUni_" & Year(Date()) & ".Tafels," & _
...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Error in Select query

(OP)
Thanks for the 'bracket solution', especially Dgillz.

I confess Andrejek : 'bad design'.
How can I allocate annually with as little (re)programming work as possible?

Thanks again to everyone.

RE: Error in Select query

Instead of TafelUni_2023 and SV999_2023_od tables, have them as TafelUni and SV999_od and add a new column to each of them to indicate a Year.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Error in Select query

You really ought to have ONE column for YEAR rather than having a column for each year.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Error in Select query

By

Quote (Me)

add a new column to each of them

what I meant was: add a new column to each of them the two tables.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close