Creating Report Using A Range

[whitebear]

Hi

I know how to create a report using a combo box where it shows me the options with a drop down menu, however I want to create a report using two combo boxes, so that the report reflects data from (for example) Supplier A to Suppler D.

Can anyone assist me with this??

Thanks

WB

 

[rainman89]

I guess this depends alittle on how you have your suppliers in your table. Does each supplier have their own ID associated with them?

 

[whitebear]

No, its basically "A Supplier" thorugh to "E Supplier". I have one combo box to show one supplier, but I want to show several on the report using the drop down menus

Hope that clarifies things!

 

[rainman89]

Hmm... So you do not have a separate supplier table? I think it would be easier to do with a separate supplier table. That way you could do a BETWEEN statement on your 2 combo boxes using supplier ID

 

[whitebear]

Its a stock list table, with the following fields:
> Item Number
> Item Description
> Stock Price
> Net Price
> In Stock (tick box)
> Supplier

I have sorted the report by supplier, but want to show a range so the user can make a selection in two drop down boxes (housing the suppliers name) and then a button creates the report.

 

[rainman89]

Ok so in your stock list table change it from
supplier to supplier ID

and then create a supplier table with

ID
supplier Name

That will make it much easier for you to do what you are asking for

 

[whitebear]

ive already got a supplier table, but the PK is the suppliers name and in the stock list table the FK is also the suppliers name. Do you still need me to add a supplier ID?

 

[rainman89]

See the attached sample

 

[rainman89]

ive already got a supplier table, but the PK is the suppliers name and in the stock list table the FK is also the suppliers name. Do you still need me to add a supplier ID?

What does your supplier table contain?

Should be something like

supplierID pk
supplierName
etc..

 

[whitebear]

ok, i've changed round the tables so that the suppliers now reads:
> Supplier ID (PK)
> Supplier
> Contact Number
> Address
> City
> Post Code

And the Stock Items Table Now reads:
> Item Number (PK)
> Item Description
> Stock Price
> Net Price
> Supplier ID (FK)

 

[rainman89]

Were you able to get the query to return the results you were looking for?

 

[whitebear]

So i now have a report that shows the suppliers name and ID. But my issues is still creating the 2nd combo box so that the report will return the range of suppliers and the stock corrosponding to that suppier

 

[rainman89]

ok so use the wizard to create your 2 combo boxes. put the supplierID and supplier name into the combo box.

In your query you will need to put

Between forms!yourmainformname.combo1 and forms!yourmainformname.combo2

You will need to change the parts in red to match what you have on your form

 

[whitebear]

[between forms]![StockItemsSuppliers]![comboSuppliers1] and [forms]![StockItemsSuppliers]![comboSuppliers2]

Does that look right?

 

[whitebear]

I've swapped it round so that between is outside the square brackets and it works!! thank you so much!

 

[rainman89]

You are welcome. Glad you got it figured out