Query to filter based on minimum value

[anto.danny]

Greetings,

I have table named stock which has a minimum level for items and stock levels at each branch, so the table would have columns Item_name, Minimum_level, North_stock, South_stock, East_stock, West_stock.

Now I want a report that shows items which have gone below the minimum level. As all the branch stock level are in the same table, I don't mind even if the report shows branch's that have stock beyond the minimum level.

I made a report and added query <=[Minimum_level] under all branch in record source.

But the report shows only items that are below minimum level at all four branch's. I want the report to show items that are below minimum level even in a single branch.

 

[arnelgp]

on your query criteria:

Where (Val(0 & [North_Stock]) < [Minimum_Level]) Or (Val( 0 & [South_Stock]) < [Minimum_Level]) Or (Val(0 & [West_Stock]) < [Minimum_Level]) Or (Val(0 & [East_Stock]) < [Minimum_Level])

 

[anto.danny]

I get "Undefined function 'where' in expression."

 

[arnelgp]

sorry about that:

Where (Val("0" & [North_Stock]) < [Minimum_Level]) Or (Val( "0" & [South_Stock]) < [Minimum_Level]) Or (Val("0" & [West_Stock]) < [Minimum_Level]) Or (Val("0" & [East_Stock]) < [Minimum_Level])

 

[anto.danny]

That's a no go, I get the same pop up..I added the query to criteria of all the branch.

 

[arnelgp]

what do you mean by "same pop up".

 

[anto.danny]

when I open the report i get "Undefined function 'where' in expression."

 

[arnelgp]

ok, can you show me your sql query, is it based on another query?

 

[anto.danny]

Where (Val("0" & [TBK Stock]) < [Minimum Level]) Or (Val( "0" & [TIF Stock]) < [Minimum Level]) Or (Val("0" & [DHN Stock]) < [Minimum Level]) Or (Val("0" & [RUH Stock]) < [Minimum Level])

I added this on criteria of all the branch.

 

[arnelgp]

ok, i see, can you also post the record source of your report.

 

[smig]

Do you create your query using the query builder? if so, can you put a picture of it?

 

[anto.danny]

I don't use query builder. I click on the record source and it opens up the query, I type the condition in the Criteria of the columns. See attachment

 

[arnelgp]

go to SQL view and paste that code:

SELECT * FROM [Parts List] WHERE (Val("0" & [TBK Stock]) < [Minimum Level]) Or (Val( "0" & [TIF Stock]) < [Minimum Level]) Or (Val("0" & [DHN Stock]) < [Minimum Level]) Or (Val("0" & [RUH Stock]) < [Minimum Level])

 

[anto.danny]

now it shows the entire list. Its like there is no condition. When you find time could you make a sample so I could understand what I'm doing wrong

 

[arnelgp]

your query will show you any branch that has its stock below minimum level. can you see in the report that it does show this? or at least one branch? if so, then our criteria is correct. or is there anything else you want to show or not to show?

 

[anto.danny]

thank you..Its working.

P.S Please be on the look out for my next thread..