Database displays products when none in stock

[Ezzz]

I have created a report that displays a "Product Summery".
The problem is that it displays all products even when there is none in stock.
Is ther a way to get the report to display only the products in stock ie. greater than zero?

[hedges98]

Simple solution would be to base the report off of a query where the criteria for stock level is >0. This should be quite straightforward but if you get stuck, post up your database structure and I/someone will be able to provide an answer!

[Ezzz]

Reports

When i say created a report i should have said "have a report" that came with the database when i down loaded it.
Hopefully i have attached a zipped copy of the database so you can see it.
I am reluctant to mess with the format cos it works for me, I can just see that later on when there is more on the database the report will be endless so it just made sence to remove all those with zero holdings? Iff its possible.
Ezzz

[hedges98]

I'm afraid I can't open your attachment because I am using Access 2000. If you can convert it, then I'll take a look.

[Ezzz]

Reports

Hopefully the attached is converted to 2000.
Ezzz

[hedges98]

Ah, this is very easy to solve!

Open the Report in Design View, Open Properties and select the Data tab. Click the three dots next to Record Source...

In the 'Units in Stock' criteria, simply enter >0

That should prevent products showing up if their stock level is 0!

[hedges98]

However, isn't there going to be problems when you want to see what products are out of stock and need to be re-ordered?

[NeoPa]

You could add criteria to the Filter property of the report.

If the field is named [Units in Stock] then the value would be :

[Units in Stock]>0

Ensure FilterOn is also set. Your report (once saved) should now always show just those records you require.

[Ezzz]

Thanks my report now works a treat.
One more question if I may as you have a copy of the database.
On the Products form is there a way to group all the like products (like unit 1 etc) together and then display them in serial number order?
Regards
Ezzz

[hedges98]

You've lost me! Could you explain what you mean more clearly please? Or an example of the output you desire

[Ezzz]

My apologies.
I have started to populate the database with various items.
There are about 6 unit 1's with different serial numbers then 6 unit 2's with different serial numbers etc up to unit 5 then 6 RFCU's with different serial numbers and so on. If I populate the database with each item in a random order thats how they appear when scrolling through the database.
What I would like is if say all the unit 1's appeared together then all the unit 2's etc and if possible they were displayed in serial number order.
I tried to link the products and inventory tables through the categories box but just ended up crashing the database.
My knowledge is limited.
Regards
Ezzz

[hedges98]

I'm about to head out of the office now so will have a proper look tomorrow but for the time being try looking at 'Order By' in the Data tab of the Form property. (If you enter in a field name in the Order By property, it will order the records in alphabetical order of that field.)

[NeoPa]

One more question if I may as you have a copy of the database.

You should understand that most experts don't download databases lightly. It's so much more time-consuming than a properly worded question. I appreciate it becomes necessary sometimes, but just so you understand, most experts will avoid it where possible.

On the Products form is there a way to group all the like products (like unit 1 etc) together and then display them in serial number order?

It sounds like you want to sort the data in the underlying record source (or the form itself using the .OrderBy property). We'd need more details if this is not enough of a pointer for you.

[Ezzz]

hedges98
I have ordered the form using [ProductName], via the data tab, as you suggested and it now works a treat. Many thanks for all your help and patience.
Regards
Ezzz