zero values

[jamespeters01]

I have an stock report based on the in's and out's of packaged stock. Without using check boxes, is there a script or a way of not showing the out of stock items in my report? Therefore when the stock figure gets to zero that certain report is left off of my report but any report with an outstanding amount is still shown.


Thanks in advance

 

[Sylvialn]

Maybe you could add a CheckBal field and have a query/sql run through the query/report first: if they have a zero balance, have that CheckBal left blank and if they do have a balance, check the field. Then when you print the report, you could have the report filter to only pull up the ones that are checked in that CheckBal field (the ones with a balance). "Try everything at least once."

 

[jamespeters01]

That still means the user physically checking a check box. That's the process I'm trying to avoid. Or is there a way the checkbal field will get automatically checked (or left unchecked) once the query has been run ?

 

[Sylvialn]

I would assume that the first query/report totals & gives you the balance. You could write an sql code to check all the balances, and if they are equal to zero, you could leave the flag alone, or if there is a balance, then the code itself can check/flag it for you. This way, you have the code checking the balances and flagging the record. Then the report can print the filter (all checked). "Try everything at least once."

 

[CosmoKramer]

In the On Format event of your detail section, put this, changing for your field names:

If Me.StockFigure = 0 Then
    Cancel = True
End If

This should prevent detail records from appearing in your report when that value is zero......

 

[jamespeters01]

Thanks. That would work perfectly, but my final value is in a subreport (delivery_report) inbedded in my stock report. So once again I'm a little stuck. Any further ideas ??

 

[CosmoKramer]

Try the same syntax in the On Format event of the detail section of your subreport.....

 

[jamespeters01]

Cosmo,

That managed to remove my subreport section of stock that has reached zero (perfect), but the main section (stock description, size etc.) is still shown !!

further ideas ?

 

[CosmoKramer]

I don't know if you can do this, but try to reference the Stockfigure value in the subreport from the detail section of your main report. In other words, change the original syntax I gave you to look for Stockfigure in the subreport and cancel the detail section if it's zero. Keep the logic in the subreport too...........

 

[jamespeters01]

Which is the best way to do reference it then ?
Sorry am relatively new to Access !!

 

[BoxHead]

James,

Why not base the report on a query where the criteria for stock figure is set to Not 0?

John

Use what you have,
Learn what you can,
Create what you need.

 

[jamespeters01]

John,

That would be perfect, but the final stock figure is not a field in my query. It is a value (Text box) calculated from 2 fields...(goods finished and goods delivered fields)which are in my query.

Any further ideas ?

 

[BoxHead]

James,

It seems you should be able to calculate remaining stock (or recalculate it) in your query.

In the QBE view, add a column by typing the folowing in the top Field cell of a new column.

   RStock: [GoodsFinished] - [GoodsDelivered]

Use your actual field names, but I'm thinking that should produce the same value as your form's textbox that uses the same formula.

When you look at the datasheet view of the query, it will have a column called 'RStock' (or whatever you want to call it). Make sure the numbers are right.

Back in design view, set the criteria for RStock to be

Not 0

HTH John

Use what you have,
Learn what you can,
Create what you need.

 

[jamespeters01]

John,

Thanks for the tips.
It does work, because I've tried it with 2 fields that are within the same query.
The problem I have is that the goods delivered are in a separate query. This is because when I have more than 2 tables in a query I start to get duplicate figures. In the query that I am viewing the report from I have my input table and my finished goods table. I initially wanted my goods delivered table in the same query, but this kept on creating duplicate entries when I viewed the report. To correct this I pulled the delivered info. from a seperate query and sub-report.

So, now when I tried your process above it only works if I show my delivery table within my query....but this then creates a double entry on all the finished entries.

I hope this is not too confusing.

?????????

 

[BoxHead]

James,

I'm not sure if this work, but it's worth a try...

RStock: [GoodsFinished] - DLookUp([GoodsDelivered],"NameOfTableGoodsDeliveredResidesIn",criteria statement)

I'm sorry, I'd like to try it out first, but I'm at work and only snatched a few minutes online. My thinking is that by using the DLookUp, you won't have to include the extra table in the query.

HTH

John
John

Use what you have,
Learn what you can,
Create what you need.

 

[jamespeters01]

John,

Thanks. I tried the DLookup, but it then started to ask me for the Delivered quantity before running the report !

Any further developments ???


James

 

[CosmoKramer]

James,

Going back to my last post - If the intent is still not to have the detail line and subreport appear when Stockfigure is 0, put something like this in the On Format event of your detail section:

If Me![YourSubreport].Report![Stockfigure] = 0 Then
   Cancel = True
End If

This will cancel the printing of the detail line and the subreport. Let me know if this helps.....

 

[jamespeters01]

Cosmo,

Thanks for your help, but it actually didn't do anything !
The reports with a zero value are still shown.

Ideas ?

 

[CosmoKramer]

I don't know what to say....I tested this in one of my reports and it worked fine; it cancelled the printing of both the detail lines and the subreport data linked to that detail......

 

[BoxHead]

James,

Sorry the DLookup didn't work out.

The only other thought I would have is to go back to the query that gave you the duplicates and deselct "Show" for the goods delivered column.

If that doesn't work, play with the First, Last, Min and Max functions.

On the QBE view menu, select 'Totals' to add the row to the grid if it isn't visible. On the goods delivered column, change 'group by' to 'first' and toggle to datasheet view to see what affect it has.


Good luck. John

Use what you have,
Learn what you can,
Create what you need.