SQL where in problem

[jan pieter]

Jan Pieter here,

I have made this sql statement in VBA where rubriek = a variable
but I dont know how to implement a variable in a nested query.


SELECT "rubriek, SUM(verkoopprijs) as prijzen
FROM voorwerp
(inner join VoorwerpInRubriek on voorwerp.voorwerpnummer = VoorwerpInRubriek.voorwerp)
inner join Rubriek on rubriek.rubrieknummer = VoorwerpInRubriek.rubriekOpLaagsteNiveau
where rubriek in (select rubrieknummer
from rubriek
where rubriek = variable name
)
group by rubriek"

Do I have to treat it as a normal select query or is there something speical that I have to do ?

Thanks in advance

- Jan Pieter

 

[namliam]

You already have Rubriek in your master query, why use a sub query for that?
General way to use variables in vba and sql

SQLVariable = "select rubrieknummer "& _ 
              "from rubriek " & _ 
              "where rubriek = " & SomeVariable & ""

above is for numbers
For text use
"where rubriek = """ & SomeVariable & """"
For dates
"where rubriek = #" & SomeVariable & "#"

A strong suggestion for you to read:
http://www.access-programmers.co.uk/forums/showthread.php?t=225837&highlight=naming+convention

Groeten uit Amsterdam

 

[jan pieter]

Thank you for you reply Namliam,

I have tried to make it a little bit more readiable and used the link you posted.
But when I want to do the nested query it gave me an error on second the select statement.

"Compile error no instruction end"

Code:

Me.[Resultaten].RowSource =
"SELECT Rubriek, sum(verkoopprijs) as prijs " & _
"FROM (dbo_voorwerp
inner join dbo_VoorwerpInRubriek on voorwerpnummer = voorwerp)
inner join dbo_Rubriek on rubrieknummer = rubriekOpLaagsteNiveau " & _
"WHERE rubriek IN ("SELECT rubrieknummer " & _
"FROM Rubriek" & _
"WHERE rubriek = " & OpslagRubrieknummer &"")"
GROUP BY rubriek

Alvast bedankt

- Jan Pieter

 

[CJ_London]

looks like you have table names and field names the same

FROM Rubriek" & _
"WHERE rubriek

they need to be different

 

[namliam]

Me.[Resultaten].RowSource = " SELECT Rubriek, sum(verkoopprijs) as prijs " & _
                            " FROM (dbo_voorwerp " & _
                            " inner join dbo_VoorwerpInRubriek on voorwerpnummer = voorwerp) " & _
                            " inner join dbo_Rubriek on rubrieknummer = rubriekOpLaagsteNiveau " & _
                            " WHERE rubriek IN ( SELECT rubrieknummer " & _
                                               " FROM dbo_Rubriek " & _
                                               " WHERE rubriek = " & OpslagRubrieknummer &")"
                            " GROUP BY rubriek "

Try something along those lines

 

[CJ_London]

if you are using the code provided by namliam then you need to alias the table in the subquery otherwise all records will be returned

Me.[Resultaten].RowSource = " SELECT Rubriek, sum(verkoopprijs) as prijs " & _
                            " FROM (dbo_voorwerp " & _
                            " inner join dbo_VoorwerpInRubriek on voorwerpnummer = voorwerp) " & _
                            " inner join dbo_Rubriek on rubrieknummer = rubriekOpLaagsteNiveau " & _
                            " WHERE rubriek IN ( SELECT rubrieknummer " & _
                                               " FROM [COLOR=red]dbo_Rubriek AS Tmp[/COLOR]" & _
                                               " WHERE rubriek = " & OpslagRubrieknummer &")"
                            " GROUP BY rubriek "