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
affiliate:
DoCmd.OpenReport strDocName, acPreview, , strCriteria
