Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Thanked 0 Times in 0 Posts

    Criteria in Open Report (Access 2000)

    Criteria in opening a report( strCriteria = jan & strLondon)

    I have a good function that open a report depending on the month

    chosen.It works if StrCriteria = jan.I want however to include also the

    choice of the affiliate( i call it afid),being strLondon or strBerlin

    etc in the StrCriteria in the following way:
    strCriteria = jan & strLondon
    and then open the report.
    However it doesnt work.If i include strLondon, i receive error.
    Is it possible at all to have strCriteria = jan&strLondon ?
    My code:

    Dim strLondon
    Dim strBerlin As String
    Dim invoicedate As Control
    Dim jan As String
    Dim month As Control
    Set month = Forms!FBenchmark![month]
    Dim afid As Control
    Set afid = Forms!F1![afid]
    strBerlin = "AND [customers.afid]=2)"
    strLondon = "AND [customers.afid]=1)"
    Set invoicedate = Forms!FBenchmark![invoicedate]
    jan = "invoicedate between #1-1-2001# and #1-31-2001# "
    If month = 1 Then
    strCriteria = jan
    End If
    strDocName = " Rmonths"
    DoCmd.OpenReport strDocName, acPreview, , strCriteria

    The source of the Report is the following
    SELECT DISTINCT Orders.paymentid, [SumOfextendedprice]*1.2 AS total,

    Orders.customerid, Orders.invoicedate, Sum([Order

    Details].extendedprice) AS SumOfextendedprice, Sum([Order

    Details].liters) AS SumOfliters, customers.CompanyName, customers.afid
    FROM ((tkind INNER JOIN customers ON tkind.kindid = customers.kindid)

    INNER JOIN Orders ON customers.Customerid = Orders.customerid) INNER

    JOIN [Order Details] ON Orders.orderid = [Order Details].OrderID
    GROUP BY Orders.paymentid, Orders.customerid, Orders.invoicedate,

    customers.CompanyName, customers.afid
    HAVING (((Orders.paymentid)>0) AND ((customers.afid)=1));

    I want to take out the last condition,namely "AND

    ((customers.afid)=1))"make it a varible as strLondon =" AND

    ((customers.afid)=1))" and making also other variable as Berlin and

    Rome.And then open a report with two conditions: the month and the

    DoCmd.OpenReport strDocName, acPreview, , strCriteria

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Sacramento, California, USA
    Thanked 1 Time in 1 Post

    Re: Criteria in Open Report (Access 2000)

    I'm not sure whether you're asking two questions or one. If you're passing the criteria in the OpenReport statement, then take out the extra paren at the end and add a space before And like this:
    <pre>Set afid = Forms!F1![afid]
    strBerlin = " AND [customers.afid]=2"
    strLondon = " AND [customers.afid]=1"
    Set invoicedate = Forms!FBenchmark![invoicedate]
    jan = "invoicedate between #1-1-2001# and #1-31-2001# "</pre>

    <hr>I want to take out the last condition,namely "AND ... <hr>
    Just take out that portion of the SQL for the recordsource entirely. You don't need it there if you're passing a WhereCondition into the report when you open it.

Posting Permissions

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