Im trying to make openreport button that selects a number of records from one table based on a compare in another. I have made an sql call in query mode that gives me the records that I want to make a report from, but I cannot convert it to vba. any pointer, solutions or heads-up are welcome


DoCmd.OpenReport strDocName, acViewPreview, , "SELECT support.SerieNr FROM support WHERE support.SerieNr IN (SELECT SerieNr FROM IT_tilmeld WHERE AfdNr = " & test)

I get a compile error when I use (), and no errors without, but the statement will not work...

The error u get is because theres an corversion type error
(vergelijking zijn van verschillende typen, je vergelijkt tekst waar men een nummer verwacht)
solution :


or do :
WHERE AfdNr LIKE " & test)


or :
WHERE AfdNr = " & 9)

(IS nummer of ziet er uit ALS tekst :-)

IS =
ALS LIKE

not sure what you mean by & 9 ??

anyway I have tried it out, but that didnt do the trick either

Try the SQL below

DoCmd.OpenReport strDocName, acViewPreview, ,("SELECT support.SerieNr FROM support WHERE support.SerieNr LIKE (SELECT SerieNr FROM IT_tilmeld WHERE AfdNr like " & test))

nope..still getting a syntax error on the )

if your field SerieNr AND AfdNr are numbers use

dim test as integer
test = 1
NOTE1 : the dim statements is an example just to make it clear, i hope

DoCmd.OpenReport strDocName, acViewPreview, ,
("SELECT support.SerieNr
FROM support
WHERE support.SerieNr = (SELECT SerieNr FROM IT_tilmeld WHERE AfdNr = " & test))




if your field SerieNr is number AND AfdNr is text use

dim test as String
test = "Front Office"

DoCmd.OpenReport strDocName, acViewPreview, ,
("SELECT support.SerieNr
FROM support
WHERE support.SerieNr = (SELECT SerieNr FROM IT_tilmeld WHERE AfdNr LIKE ' " & test & "'" ))


NOTE2 : LIKE ' " & test & "'" equals
LIKE ' " & test & " ' "

check for duane's and shurik's reply at visualbasicforum

I finaly made it work..thanx for the inspiration...
the working solution looks like this

Dim test As String
Dim strDocName As String
Dim FilterCriteria As String

test = Me!AfdNr

strDocName = "Service_og_support_liste"


FilterCriteria = "SELECT DISTINCT [support].[ServiceNr] FROM support WHERE (((support.SerieNr) In (SELECT SerieNr FROM IT_tilmeld WHERE AfdNr = '" & test & "')))"

DoCmd.SetWarnings False
DoCmd.OpenReport strDocName, acViewPreview, FilterCriteria