Need help with a critiria

[David_Skov]

I hope the answer is simple

If I set up a critiria were the user should choose a category - weeks or products. I know I can use this in a query:

[Choose the week:]

And it works fine, but what if I just wants to show all - as if I removed the critiria from the query? If I choose 40 in the above example, I get the result for week 40, but if I let it blank I get nothing, but I would like to see for all weeks

The above might be doable by math, but what with categories like shampoo and Toothpaste? If I leave blank I get no result

Help plz ´

in advance tx

 

[Tanis]

Sounds like you are trying to do a search on multiple fields. If so, I suggest you use the Query By Form technique as outlined on the Microsoft Knowledge Base.

http://support.microsoft.com/default.aspx?scid=kb;en-us;95931

 

[Philem]

If you have the Northwinds example db, drop this SQL into a blank query. It shows one method to do what you are asking.

SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate, Orders.ShipCountry
FROM Orders
WHERE ((([Enter the country: Example: 'USA']) Is Null)) OR (((Orders.ShipCountry)=[Enter the country: Example: 'USA']) AND (([Enter the country: Example: 'USA']) Is Not Null));

HTH
P

 

[David_Skov]

Tx I will try that one and sorry for the late reply. Has been on xmas vac

 

[Soulac]

I've come accross this problem on a number of occasions usuall when i want to enter am or pm or leave blank for all and got the same result as you . the best explanation of the results reqd i found were on http://support.microsoft.com/default.aspx?scid=kb;en-us;10318

 

[Andrew Fergus]

Hi David,

Have you tried something like this in your query :

iif(IsNull([Choose week]), IsNotNull , [Choose week])

Please note that I haven't tested this (this PC does not have Access). If it doesn't work you might also be able to use an asterisk (e.g. Like *?) instead of the 'IsNotNull' bit.

HTH, Andrew.