Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open a month (Access 2000)

    I want to open a report for a given month chosen from the option in the form as shown in my attachement. How can i refer to this option ? Ihave attached my database

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open a month (Access 2000)

    Change the code for the On Click event of CmdDetails to

    Private Sub CmdDetails_Click()
    Dim strWhere As String
    DetailsOnly = False
    If Not IsNull(Me.Monaten) Then
    strWhere = "Month(InvoiceDate) = " & Me.Monaten
    End If
    DoCmd.OpenReport "rptGain", acViewPreview, , strWhere
    End Sub

    and similar for CmdSummary. The code checks whether the user has selected a month in the Monaten option group, and if so, it defines a string to be used as WhereCondition argument in the DoCmd.OpenReport line.

    See attached version.

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open a month (Access 2000)

    The code is perfect ! How could i add on another Whete condition for the office as seen in my attachement ? ICould you help me build this complicated interplay between the choice of two otpion boxes ?
    The record source of the report is :

    SELECT orders.paymentid, orders.invoicedate, [order details].extendedprice, IIf([cartons]>0,[ddp]*[cartons]*[size]*[pack],IIf([cartons]=0,[ddp]*[size]*[quantity],0)) AS NetPrice, [extendedprice]-[Purchaseprice] AS Delta, [delta]/[extendedprice] AS Pct, [ddu]*2 AS ddp, IIf([Size]=1,0.138,IIf([Size]=4,0.552,IIf([Size]=18,2.48,IIf([size]=20,2.66,IIf([size]=60,6.27,IIf([size]=180,6.18,IIf([size]=205,19,0)))))))*[cartons] AS surcharge, [NetPrice]+[surcharge] AS PurchasePrice, products.grade, products.size, [order details].liters, [order details].cartons, [order details].Quantity, Customers.Customerid, Customers.afid
    FROM ((Customers INNER JOIN orders ON Customers.Customerid = orders.customerid) INNER JOIN [order details] ON orders.orderid = [order details].OrderID) INNER JOIN products ON [order details].ProductID = products.Productid
    WHERE (((orders.paymentid)=True))
    ORDER BY orders.invoicedate DESC;

    And the where condition should contain customers.afid = me!office



    kind regards

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open a month (Access 2000)

    Try this:

    Private Sub CmdDetails_Click()
    Dim strWhere As String
    DetailsOnly = False
    If Not IsNull(Me.Monaten) Then
    strWhere = " AND Month(InvoiceDate) = " & Me.Monaten
    End If
    If Not IsNull(Me.Office) Then
    strWhere = strWhere & " AND afid = " & Me.Office
    End If
    If Not strWhere = "" Then
    ' Remove first " AND "
    strWhere = Mid(strWhere, 6)
    End If
    DoCmd.OpenReport "rptGain", acViewPreview, , strWhere
    End Sub

    I can't actually test the code since you haven't included the tables and query in your database.

    The code uses a nice 'trick' that is very handy when you have to concatenate an unknown number of conditions. Each of the parts starts with " AND ", so if at least one condition applies, the combined condition strWhere will start with " AND " too. For example:

    " AND Month(InvoiceDate = 11 AND afid = 4"

    Before passing strWhere to the report, the first " AND " is removed by the line

    strWhere = Mid(strWhere, 6)

    In the example, this would leave

    "Month(InvoiceDate = 11 AND afid = 4"

    which is the where-condition we want to apply.

  5. #5
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open a month (Access 2000)

    Yes Hans, it works ! I cannot find words to thank you. I am near to completing my task and i am leaving the Lounge with gratitude. but i want once again to show my great esteem. Without you i woulnd have solved this problem so brilliantly !

  6. #6
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open a month (Access 2000)

    Dear Hans

    In order to compare profits with outlays i have made a subreport and i think i should give the where command still in cmddetails part.Could you have a look at my database.Actually the subreport values should appear only when i have cjosen an office

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open a month (Access 2000)

    You haven't linked the subreport to the main report. See <post:=615,945>post 615,945</post:>.

    You can add the following code to the Report_Open event procedure of rptGain:

    If IsNull(Forms!frmGain!Office) Then
    Me.rptOutlays.Visible = False
    End If

  8. #8
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open a month (Access 2000)

    Dear Hans

    You have helped me open the report with the two conditions for months and for the offices and it works great.If i want to open the report without any criteria, where should i exactly put the
    line to open the report ?

    DoCmd.OpenReport "rptGain", acViewPreview

    The OnClick event that is working :
    Dim strWhere As String
    DetailsOnly = False
    If Not IsNull(Me.Monaten) Then
    strWhere = " AND Month(invoiceDate) = " & Me.Monaten
    End If
    If Not IsNull(Me.office) Then
    strWhere = strWhere & " AND afid = " & Me.office
    End If
    If Not strWhere = "" Then
    ' Remove first " AND "
    strWhere = Mid(strWhere, 6)
    End If
    DoCmd.OpenReport "rptGain", acViewPreview, , strWhere

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open a month (Access 2000)

    You can create a new button to open the report without criteria:

    Private Sub cmdOpenStraight_Click()
    DoCmd.OpenReport "rptGain", acViewPreview
    End Sub

  10. #10
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open a month (Access 2000)

    But i have a code in the onopen event of the report:
    If Forms!frmGain.DetailsOnly = True Then
    Me.Detail.Visible = False
    End If
    End Sub

    Shall i create a new report ?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open a month (Access 2000)

    You can set DetailsOnly in the On Click code of the new command button on frmGain. Or uou can create two new command buttons on frmGain: one sets DetailsOnly to True and opens the report without conditions, the other sets DetailsOnly to False and opens the report without conditions.

  12. #12
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open a month (Access 2000)

    Thank you !

Posting Permissions

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