Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jan 2008
    Posts
    74

    Question Unanswered: Print Report based on Search Form results

    Hi folks,

    I have a fairly advanced search form, which displays the results in a subform. The search is triggered by a button click, which has a VBA code with embedded SQL string as event.

    My task is to add a 'Preview Report' button to the form, which opens and prints a report based on form's results. So far, I've created a report with all the necessary fields, but I can't get to narrow down the displayed results to those on the form. Here is my code so far:

    Code:
    :Private Sub OpenReport_Click()
    
    
        ' The major part of this code is duplicated from the code behind the form's SEARCH button. 
    
    Dim strSQL, sSQL As String
    
    'Narrows down results depending on which checkboxes are checked
        
        If Me.chck1 = -1 Then
            strSQL = strSQL & " tbl_RequestTracker.[Request Source] = 'SSO-CR' OR "
        ElseIf Me.chck1 = 0 Then
            strSQL = strSQL & ""
        End If
    
        If Me.chck2 = -1 Then
            strSQL = strSQL & " tbl_RequestTracker.[Request Source] = 'HS-CR' OR "
        ElseIf Me.chck2 = 0 Then
            strSQL = strSQL & ""
        End If
    
        If Me.chck3 = -1 Then
            strSQL = strSQL & " tbl_RequestTracker.[Request Source] = 'Client Server' OR tbl_RequestTracker.[Request Source] = 'Network Data' OR tbl_RequestTracker.[Request Source] = 'Mainframe Data' OR "
        ElseIf Me.chck3 = 0 Then
            strSQL = strSQL & ""
        End If
    
    'The actual SQL string with check boxes and date range comboboxes as parameters. It isn't based on a query.
    
        sSQL = "SELECT tbl_RequestTracker.[Request Source], " & _
             "tbl_RequestTracker.ReqNr, tbl_RequestTracker.[Brief Description]," & _
             "tbl_RequestTracker.[Requestor Name], " & _
             "tbl_RequestTracker.Estimator, " & _
             "tbl_RequestTracker.[Originator Cost Centre] FROM tbl_RequestTracker " & _
             "WHERE (((tbl_RequestTracker.[Date Submitted]) " & _
             "Between [Forms]![FrmOrganisation_New]![cboFrom] And [Forms]![FrmOrganisation_New]![cboTill])) " & _
             "AND " & Left(strSQL, Len(strSQL) - 3) & ";"
        
        
     'open report and set filter to the sSQL - query results
    
       DoCmd.OpenReport "rptOrganisation_New", acViewPreview, , sSQL
        
    
    End Sub
    Any help would be greatly appreciated!

    Many thanks,
    OD

  2. #2
    Join Date
    Aug 2006
    Posts
    559
    Where are you putting in your source from the subform for the report?

  3. #3
    Join Date
    Jan 2008
    Posts
    74
    The source for the report is the sSQL at the end of the OpenReport statement.

    is there a better way to do this? because my code isn't working.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    These are pointless and completely unnecessary: strSQL = strSQL & ""

    Code:
        If Me.chck2 = -1 Then
            strSQL = strSQL & " tbl_RequestTracker.[Request Source] = 'HS-CR' OR "
        ElseIf Me.chck2 = 0 Then
            strSQL = strSQL & ""
        End If
    and

    Code:
        If Me.chck2 = -1 Then strSQL = strSQL & " tbl_RequestTracker.[Request Source] = 'HS-CR' OR "
    Are equivalent.

    Opening the report can be done just like you are doing it, but the SQL needs to only have the WHERE clause, without the word WHERE... not the whole SQL.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Jan 2008
    Posts
    74
    So, my SQL string for the report would look like as below? :

    Code:
    (tbl_RequestTracker.[Date Submitted]) " & _
             "Between [Forms]![FrmOrganisation_New]![cboFrom] And [Forms]![FrmOrganisation_New]![cboTill])) " & _
             "AND " & Left(strSQL, Len(strSQL) - 3) & ";"

  6. #6
    Join Date
    Jan 2008
    Posts
    74
    I'm trying to make it run with the SQL-string expression as posted above, but I'm getting extra parentheses, and no idea from where. So, the whole string is wrapped in parentheses.

    How do I remove the parentheses that aren't even in the original string??
    Getting pretty desperate here... Usually I'm not begging but please? Anyone any idea???

    Thanks,
    OD

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ((((((((Access) sure) (has) a) way) with) brackets))).

    I'd be trying this:

    Code:
    "[Date Submitted] Between #" & [Forms]![FrmOrganisation_New]![cboFrom] & _
    "# And #" & [Forms]![FrmOrganisation_New]![cboTill] & "#;"
    Good luck!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Jan 2008
    Posts
    74
    Stupid brackets are still there!!

    My SQL looks as follows:

    Code:
    sSQL = "tbl_RequestTracker.[Date Submitted] " & _
             "Between #" & [Forms]![FrmOrganisation_New]![cboFrom] & _
             "# And #" & [Forms]![FrmOrganisation_New]![cboTill] & _
             "# AND " & Left(strSQL, Len(strSQL) - 3) & ";"
    Access displays "Syntax error in query expression: "
    Code:
    '(tbl.[Date Submitted] Between #5/4/2008# 
    And #7/16/2008# AND tbl.[Record Source] = 'SSO-CR';)'

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yup... it's a syntax error alright... there can't be a closing bracket after the semicolon. Question really is where are those brackets coming from?!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    in your original post you have a missing space:
    Code:
    ...tbl_RequestTracker.[Brief Description]," & 
                                             /\
                                            here
    BTW the closing ";" is conventional but usually not essential.
    have a go without
    & ";"
    and see what error you get

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Good idea IZY... I forget that Access doesn't actually need the semicolon.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  12. #12
    Join Date
    Jan 2008
    Posts
    74
    Hallelujah, I removed the semicolon, and it works now!
    Thanks so much to all!

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Good! I'm still curious where the brackets came from though.

    Are the semicolons required in SQL Server or MySQL?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i just looked thru the code of several apps and noticed that i always use closing ; with MSSQL & MySQL and rarely use it with Jet - i really hadn't thought about it before your question.

    i believe that closing ; is mandatory with MySQL but entirely optional with MSSQL (at least up to MSSQL-2000).

    browsing the net out of curiosity, i see suggestions that closing ; is now a required multi-statements separator for MSSQL-2005 --- cannot test to confirm since my 2005 demo has expired.

    izy
    currently using SS 2008R2

  15. #15
    Join Date
    Jan 2008
    Posts
    74
    Yes, ; is mandatory for MSSQL 2005. At least, it is for my version.

Posting Permissions

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