Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2003
    Posts
    4

    Unanswered: DoCmd.OpenReport with nested sql

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

  2. #2
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Lightbulb try LIKE operator

    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

  3. #3
    Join Date
    Dec 2003
    Posts
    4
    not sure what you mean by & 9 ??

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

  4. #4
    Join Date
    Nov 2003
    Posts
    76
    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))

  5. #5
    Join Date
    Dec 2003
    Posts
    4
    nope..still getting a syntax error on the )

  6. #6
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450
    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 & " ' "

  7. #7
    Join Date
    Oct 2002
    Location
    Philippines
    Posts
    15

    Re: try LIKE operator

    Originally posted by Marvels
    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
    check for duane's and shurik's reply at visualbasicforum

  8. #8
    Join Date
    Dec 2003
    Posts
    4
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •