Radar said:
Not sure which group this one falls in .
I have a shipping report with a list of venders that are sorted by
delivery days out.
My days out field is in my rpt Page header. If I query for vendors
that are 3 days out. It shows the #3.
My question is! What if I query Days 1,2,3 (it will only display one
of these numbers.
How do I Have the Place a field to display 1,2,3 or what every
combination I choose.
Thanks
Radar.
Brendan Roberts gave a good answer, assuming that you know ahead of time
which combination you want. But suppose you want to choose a different
combination each time you run the Report? One way to do that (not
perhaps the best possible) is to have a Table with yes/no checkboxes
that can be set up before running each Report.
For example, suppose the list of expected deliveries looks like this
(although it's a Table in this example, it would normally be a Query
based on expected delivery dates and knowledge of what today's date is):
[Deliveries]
DaysOut Vendor
------- ------
10 GreenSlime
2 Acme
1 Yummy
4 BrandX
2 Jerx
Then we could have a Table in which the [IsReported?] field could be
used to select the specific day numbers to be listed in the Report:
[Days]
Number IsReported?
------ -----------
1 Yes
2 No
3 No
4 No
5 Yes
6 Yes
8 No
10 Yes
20 Yes
Which of those possible numbers happen to be selected could be returned
by the following Query:
[Q_Selected]
SELECT Days.Number
FROM Days
WHERE (((Days.[IsReported?])=Yes))
ORDER BY Days.Number;
Number
------
1
5
6
10
20
.... and a list of the vendors whose deliveries are expected on any one
of those days would be returned by this Query:
[Q_VendorsByDay]
SELECT Deliveries.Vendor, Deliveries.DaysOut
FROM Days INNER JOIN Deliveries
ON Days.Number = Deliveries.DaysOut
WHERE (((Days.[IsReported?])=Yes))
ORDER BY Deliveries.DaysOut;
Its output would look like this:
Vendor DaysOut
----------- -------
Yummy 1
GreenSlime 10
It wasn't clear to me from the message if a list of the selected days
were desired in the Report. Assuming it is, the following VBA function
would produce that list (based on the [Q_Selected] Query):
'Return a comma-separated list of all
' selected choices of delivery dates
Public Function DaysList() As String
Dim rsQuery As Recordset
Dim strList As String
Set rsQuery = CurrentDb.OpenRecordset("Q_Selected")
Do While Not rsQuery.EOF
'Grab the next selected value
strList = strList & ", " & rsQuery.Fields(0)
rsQuery.MoveNext 'Next record, if any
Loop
'Delete the leading ", "
DaysList = Mid$(strList, 3)
rsQuery.Close 'Clean up
Set rsQuery = Nothing
End Function 'DaysList()
Having defined these Queries and this function, you could then design a
Report whose Record Source is [Q_VendorsByDay] to display the results.
In this Report, the Report Header contains a Text Box whose Control
Source property is =DaysList() , the function we defined:
+---------------------------------------+
| Days reported: 1, 5, 6, 10, 20 |
| |
| DaysOut Vendor |
| ------- ---------- |
| 1 Yummy |
| 10 GreenSlime |
| |
| |
+---------------------------------------+
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
