Access report/query help

[fh786]

Hi,

I am trying to create a report where the user inputs a start date and end date and then selects a supplier from a drop down list and this creates a report.

I have got the date bit working but how do I create a parameter box with a drop down list for the suppliers?

 

[fneily]

There's about 7 Access forums. You'd want to post this in
Microsoft: Access Queries and JET SQL

You'd have a form with two textboxes to pick the date. When a user clicks on a textbox, a calendar would open so the user can pick the date. You don't want the user typing in dates - typo problems. Search the access forums to see how to create the calendar.
You'd also have a dropdown box for the suppliers.

You'd create a query to filter. Since you say you have the date part working, the criteria for the supplier field would look like:
Forms![YourFormName]![DropDownName]
Note the selection form must remain opened.

 

[fh786]

Thanks I will post it in the relevant forum.

 

[fh786]

Thanks for the info fneily, the way I am getting it to work at the moment is with a query but cannot get a drop down list of suppliers with a parameter box.

To get the date working I have the following code; Between [txtDateFrom] AND [txtDateTo] in the criteria I just wanted to know what to put in for suppliers so it reads the suppliers table and users can pick from a drop down list?

Any ideas would be great!

 

[fneily]

You can't hide from the Access gurus, they browse this forums also.
Create a form with a combobox on it. Save it and name it. The Rowsource of the combo will be:
SELECT DISTINCT [supplier] FROM [Supplier_Table] ORDER BY [supplier];

Column Count 1
Bound Column 1

Create a query from your table. Bring down the date field and supplier field. Under the date field, use the between function, as you did. Under the supplier field, on the criteria line put:
[Forms]![supplier_form]![combo2]

Create a command button on the form using the Command Button Wizard. Select Miscelaneous on left, Run Query on right. Click Next, select your query. Finish the wizard steps.

The user will open the form with the combo, make a selection, click the command button which will run the query. They will be prompted for the dates. The supplier will be picked up from the form's combobox. The form must stay opened.

 

[fneily]

Forgot to mention: If you want to go from Intro. to Access to a more professional look, again, have two textboxes that when clicked on will bring up a calendar. You don't want users typing. Here's what I use:

http://www22.brinkster.com/accessory/forms/005.shtml

Every company I did contract work for likes this feature. I think Microsoft incorporated it in Access 2007.

Also, for the report, maybe there wasn't a supplier in that time period so you'll have no data from the query. You can put code on the On No Data event of the report that will show the user a message box stating that supplier has no data for the time period.

 

[fh786]

Thanks for the info once again I will give it a go.