query criteria help

[shutzy]

i have just tried to amend my query. the idea is that the initial results are to show all items that are instock. ie have a value >0. if i click a check box i want to display all items that are available regardless of stock.

so my criteria expression is as follows

IIf([Forms]![frmDepartures-AddRetail]![checkShowAllAvailable]=True,([tblItems].[StockQTY])<999,>0)

however this does not show the desired results. the results seem to be ignoring the > and < and refering to it as =. if i have items that are 0 stock they will show on false but all other items do not show on true
why is the query ignoring the > and < please

thank you

 

[namliam]

You cannot use an IIF in the where clause in that manner, to do what you are trying to do try:

([Forms]![frmDepartures-AddRetail]![checkShowAllAvailable]=True and [tblItems].[StockQTY] <999) or 
([Forms]![frmDepartures-AddRetail]![checkShowAllAvailable]=False and [tblItems].[StockQTY] >0)

Wont give you the results you are looking for, any stock over 999 will not be shown, but it is the proper syntax

 

[shutzy]

namliam: thanks for that. im not really trying to get the stock that is lower than 999 but to get all of the stock. would the correct one be NZ?

 

[namliam]

The correct way would be to NOT have a limit at all on stock if you dont want to have a limit on stock.

i.e.

([Forms]![frmDepartures-AddRetail]![checkShowAllAvailable]=True ) or 
([Forms]![frmDepartures-AddRetail]![checkShowAllAvailable]=False and [tblItems].[StockQTY] >0)

 

[namliam]

Just fyi building queries with form references can get ugly fast, try researching something called "Filter by form" to adjust your queries on demand to the exact requirements you need

This may be a starting point:
http://www.access-programmers.co.uk/forums/showthread.php?p=1300908#post1300908

 

[shutzy]

the query is behind a lst box on a form so it wont be referencing anyother form anyway. but i will look at the link for other queries i have done and duplicated with a different form name.

thank you

 

[namliam]

It isnt about the amount of forms perse but more the amount of controls and the different results you may want to achieve from mix and matching different controls

Now you have one control, 2 options
2 controls each 2 options, 4 options
3 controls each 2 options, total 8, next 16 and 32, 64, this is with just checkboxes not even bothering with dates or anything special.