Populating ListBox from Parameter Query 1

[activethistle]

This is driving me nuts!!!

It's probably very simple...

I have a form with two input boxes a button and a list box.

I want my users to enter two dates in the text boxes and click the button.

As a result I want to run a query that extacts the number of times an item (advertising source in actuality) occurs for the period between the two dates, and puts it in the list box.

e.g.

 ----------       ----------
| 01/01/01 |     | 01/03/01 |
 ----------       ----------

          _________
         |         |
         |Click Me!|
         |_________| 



   ------------------------
  | Times        |    5    |
  | Guardian     |   10    |
  | Telegraph    |    7    |
  |              |         |
  |    etc...etc...        |
   ------------------------

Does anyone have any ideas before I go completely gaga???

P.S. If you can offer a subform alternative I shall be equally grateful.

TVMIA

Active Thistle :{}

 

[RickSpr]

You just need a totals query.
1. Create a new query based on your source table or query.
2. Click View>Totals on the menu to reveal the Totals: line.
3. Drag the advertising source into the grid.
4. In the second column of the grid, enter "Count: 1" in the Field: line, and choose Sum in the Total: line.
5. Drag your date down to the third column, choose Where in the Total: line, and enter "Between [Begin Date] And [End Date]" in the Criteria: line.
6. Save this query, and enter its name as the Row Source for your list box.

When you open your form, the query will ask for Begin Date and End Date. After you enter them, the list box will contain your advertising sources and a count of records found for each within the date range given. Rick Sprague

 

[activethistle]

EXCELLENT!!! Thanks Rick.

I still have a small problem though. When it comes to repeating the process, I have to keep closing and reopening the form. I suppose I could put a button on the form that did that for me, but is there any way that I could use two textboxes on the form and a button to submit the texbox values to the query directly?

TVVMIA

:{}

 

[RickSpr]

Sure thing! In this case, you don't want to use an Access query. Leave the list box's Row Source Type set to Table/Query, but put the following in the Row Source:

    SELECT [AdSource], Count(*) As Counter FROM MyTable WHERE MyTable.[SomeDate] BETWEEN Forms!ThisForm![txtBeginDate] AND Forms!ThisForm![txtEndDate] GROUP BY [AdSource] ORDER BY [AdSource];

Put two text boxes, called txtBeginDate and txtEndDate, on your form. In the command button's Click event procedure, requery the list box with code like this:
lstSources.Requery

I'm in a hurry and don't have time to test it, but that's about what you want. I'll check back in case you have a problem with this. Rick Sprague

 

[activethistle]

Thanks Rick - Nearly there, but the query doesnt seem to be taking any notice of the text boxes.
It just keeps throwing up the parameter entry boxes.

What am I doing wrong??

TIA