[jhobbs]

I'm having some trouble running a query. I have a db that has a lot of sales information including store number and category. I'm trying to run a query where I can pick the dates, but also select either all stores for a certain category or a certain stores and all categories (or any combination of certain store and category). Attached is the code I am currently using that works showing all stores and categories by selecting a certain month.

Thanks for any assistance you can provide.

[r937]

1. you did not say what the problem is

2. your query formatting is ugly

yes, i know Microsoft Access throws the extra parentheses in for you, but you simply must take responsibility for the parentheses yourself

i've done a quick analysis of your second query and here's what it says:

WHERE (
      HIDATA60404.ELSDATE Between [Beginning Date] And [Ending Date]
  AND [HIDATA60404]![ELSSTORE] Like [store]
  AND [HIDATA60404]![CATEGORY] Like [cat]
      ) 
      
   OR (
      [store] Is Null
  AND [cat] Is Null
      )

my questions to you: are you sure you want that particular logic, and are [store] and [cat] actual column names?

[jhobbs]

Sorry if I'm not too descriptive. It's been a while since I've used access. [store] and [cat] are both paramaters that allow the user to enter a certain store # or category #. My current problem is whenever I try to choose a store and select all categories. I return all stores and all categories. The same happens when I try to select all stores and a certain category. I thought that's what the "null" statement was used for?

[xarfox]

null means that the field has no value

[jhobbs]

I guess my question is, If I have parameters they can select say 1 certain store number, how can they select all of them?

[xarfox]

I'm trying to run a query where I can pick the dates but also select either all stores for a certain category
SELECT * FROM StoreTable WHERE SalesDate = '1/30/2000'

a certain stores and all categories (or any combination of certain store and category).
SELECT * FROM StoreTable WHERE SalesDate = '1/30/2000' AND StoreName = 'Philadelphia'

SELECT * FROM StoreTable WHERE SalesDate = '1/30/2000' AND StoreCategory = 'Retail'


I'm not sure what your table structures or data looks like, so i put some example code up, not sure if thats what you wanted

i tried to read your attached document, but immediatley had an anyuresm upon reading it -- nice formatting

[jhobbs]

I understand the code is ugly...i'm not a programmer or database person and it works which is good enough for me. Currently When you try to run this query it pops up and asks what beginning date and ending date (which obviously shows everything between those dates) and asks what store number you want. It works if you put in valid dates and a certain store number, it also works if you leave the store number blank because it shows all stores. When I try to make it ask what category you want and use the same logic as the store number, it always puts all category numbers rather than the one i am choosing. On a side question, how could I choose more than 1 store number in my query.