Prev: Can't get my head around this query..help!
Next: Extracting data from string
From: Charlie on 13 Apr 2010 05:40
HI,

I have a query that shows all the sales against all sales reps and another
with all sales against a specified rep, what I need is sales against a
selection of reps. we have 5 reps and I need the results for 3 of them in
one report.

Any help would be appreciated.

Thanks in advance

Charlotte


From: KenSheridan via AccessMonster.com on 13 Apr 2010 07:22
Charlotte:

To give you maximum flexibility I'd suggest basing the report on the query
which returns all reps, then open the report from a dialogue form which
filters it on the basis of selections in a multiselect list box. Assuming
you have a table SalesReps with columns SalesRepID (the primary key),
FirstName and Lastname, create the dialogue form and add a list box setting
it up as follows:

For its RowSource property:

SELECT [SalesRepID], [FirstName] & " " & [LastName] FROM [SalesReps] ORDER BY
[LastName], [Firstname];

For other properties:

Name: lstSalesReps
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm (Access will automatically convert these to inches
if you are not using metric units)
MultiSelect: Simple or Extended as preferred.

Add a button to the form to open the report, called rptSales in this example,
with the following in its Click event procedure:

Dim varItem As Variant
Dim strSalesRepIDList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.lstSalesReps

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSalesRepIDList = strSalesRepIDList & "," & ctrl.ItemData
(varItem)
Next varItem

' remove leading comma
strSalesRepIDList = Mid(strSalesRepIDList, 2)

strCriteria = "[SalesRepID] In(" & strSalesRepIDList & ")"

DoCmd.OpenReport "rptSales", _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
MsgBox "No sales reps selected", vbInformation, "Warning"
End If

To open the report you'd open the dialogue form select one or more sales reps
in the list box and click the button. The report will then open filtered to
the selected reps. Note that the SalesRepID column must be in the report's
underlying RecordSource, though not necessarily shown in the report.

Ken Sheridan
Stafford, England

Charlie wrote:
>HI,
>
>I have a query that shows all the sales against all sales reps and another
>with all sales against a specified rep, what I need is sales against a
>selection of reps. we have 5 reps and I need the results for 3 of them in
>one report.
>
>Any help would be appreciated.
>
>Thanks in advance
>
>Charlotte

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201004/1

 | 
Pages: 1
Prev: Can't get my head around this query..help!
Next: Extracting data from string