27. november 2003 - 13:13Der er
13 kommentarer og 3 løsninger
SQL query Vs SQL i vba
Jeg har lavet et statement der ser ud som følger SELECT SerieNr FROM table1 WHERE felt1 IN (SELECT felt1 FROM table2 WHERE felt2 = '0200'); jeg får den ene værdi jeg søger så¨det er fint nok, men overfører jeg den til vba i access2k hvor jeg mener den skal se ud som neden for:
Dim strSQL As String Dim test As String test = "0200" Dim strDocName As String
strDocName = "rpt_name"
strSQL = "SELECT SerieNr FROM table1 WHERE felt1 IN (SELECT felt1 FROM table2 WHERE felt2 = test)"
I dette særtema ser vi på, hvordan cloud og AI bliver fundamentet for virksomhedernes digitale forretning, og hvordan de nye muligheder for automatisering og forretningsværdi kan udnyttes uden at miste overblik, sikkerhed og menneskelig kontrol.
det er netop det der er problemet for selve sql sætningen virker fint, men den/de værdier skulle gerne smides ind i openReport sætningen som where clause, og det kan jeg simpelthen ikke få den til..
vb-wanna-b>Does your nested SQL (SELECT Felt1 FROM table1 WHERE felt2 = '0200') return ONE value or can it return more than one? If it only returns one value then use the dlookup function
I am sure it is possible to use the WhereCondition parameter but it sis obvioulsy important that the SQL is correct. If must NOT start with WHERE tahts for sure!
I have testet the SQL i query mode, and it can return more than one entry, the nested SQL I made to begin with that is. But I cannot get the open report to accecpt the one+ values I get in the sql statement
SELECT felt1 FROM table1 WHERE felt1 IN (SELECT felt1 FROM table2 WHERE felt2 = '0200'); (in access query mode. It is the query I base my OpenReport from) that I made to begin with, but it looks like the nested SQL will not funktion in the OpenReport sentence
that works fine in query mode, it is the same as in my previous post...It gives the same result anyway, I want the filtered result in table2 to be the values of the records in table1 to be processed in the report...it late..so that might sound wierd.... I thougt of something that im going to try on for size tomorrow - I´ll let you know what that turnes out like.. this is what Im going to test: DoCmd.OpenReport strDocName, acViewPreview, , ("SELECT support.SerieNr FROM support WHERE support.SerieNr = (SELECT SerieNr FROM IT_tilmeld WHERE AfdNr LIKE ' " & test & "'" ))
ok, I finaly found a way to solve it. If I define a filter criteria based on my SQL as a string and include that in the openreport sentence and leave out the where clause completely, it works, once again the solution presented it self right under my nose, only this time I managed to see it...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
