Error in Select query

[leifoet]

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

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.

 

[SkipVought]

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


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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!

 

[leifoet]

Thank you SkipVought for your response.

"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.

 

[mikrom]

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.

 

[dgillz]

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:

"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:

http://www.gainfocus.biz/exceladdin.html

 

[Andrzejek]

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

Something like:
[pre]
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"
[/pre]

---- Andy

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

 

[SkipVought]

...
"FROM 2023_TafelUni TU " & _
"left JOIN 2023_SV999_od [highlight #FCE94F]SV[/highlight] ON TU.Tafels = SV.Tafel " & _
"ORDER BY TU.Tafels, SV.Clb, SV.name ASC"


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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!

 

[mikrom]

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:

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

 

[Andrzejek]

I also suspect we have [highlight #FCE94F]2023[/highlight]_SV999_od and [highlight #FCE94F]2023[/highlight]_TafelUni tables because of AD 2023, and next year leifoet will have [highlight #FCE94F]2024[/highlight]_SV999_od and [highlight #FCE94F]2024[/highlight]_TafelUni tables, and so on every year... Bad design :-(

---- Andy

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

 

[Chris Miller]

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

 

[Andrzejek]

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

I would hate to maintain code like:
[tt]
strSQL = "select " & _
" SV999_" & Year(Date()) & "_od.*, TafelUni_" & Year(Date()) & ".Tafels," & _
...[/tt]

---- Andy

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

 

[leifoet]

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.

 

[Andrzejek]

Instead of [tt]TafelUni_[red]2023[/red][/tt] and[tt] SV999_[red]2023[/red]_od[/tt] tables, have them as [tt]TafelUni[/tt] and [tt]SV999_od[/tt] and add a new column to each of them to indicate a Year.

---- Andy

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

 

[SkipVought]

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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!

 

[Andrzejek]

By

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