Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2004
    Posts
    20

    Unanswered: Is Null, Is Not Null Statement

    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.
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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:
    Code:
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2004
    Posts
    20
    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?

  4. #4
    Join Date
    Jun 2004
    Location
    Philly
    Posts
    51
    null means that the field has no value

  5. #5
    Join Date
    Jun 2004
    Posts
    20
    I guess my question is, If I have parameters they can select say 1 certain store number, how can they select all of them?

  6. #6
    Join Date
    Jun 2004
    Location
    Philly
    Posts
    51

    Thumbs up

    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

  7. #7
    Join Date
    Jun 2004
    Posts
    20
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •