Results 1 to 8 of 8

Thread: Access Query Display where balance may be nil, positive or both

  1. #1
    Join Date
    Aug 2002
    Posts
    6,223
    Rep Power
    0

    Default Access Query Display where balance may be nil, positive or both

    I have a query with the following column (the +1 is in order to make the calculation non-negative):

    Code:
    ([balance]<=0)+1
    I want a criteria whereby the user can display either:

    1. Where balances are all 0
    2. Where balances are 1 or greater
    3. All balances.

    I came up with the following input message box, but inputting * for all balances gives no results at all:
    Code:
    [In stock only? 1=YES, 0=ZERO ONLY, *=ALL]
    Any help given will be appreciated.
    .
    PC - Ubuntu 15.04 64bit Desktop
    HP Pav G60-236US 3GB RAM Laptop, Ubuntu 15.04 64bit and Win7 Home

    "So Daddy, how come you telling me stealing not right when YOU copying DVDs? How come? How Come?"


    RIP Ramesh ...

  2. #2
    Join Date
    Jan 2007
    Posts
    164
    Rep Power
    18

    Default

    You need to be more specific. Not understanding what you are asking. What have you tried so far and what errors have you encountered?

  3. #3
    Join Date
    Aug 2002
    Posts
    6,223
    Rep Power
    0

    Default

    No error messages, just need to have the option to either have the query return results:

    1. Where balances are all 0
    2. Where balances are 1 or greater
    3. All balances.

    So far, I've been able to get 1 or 2, but not 3.

    The balance variable is a result of subtracting sold from initial quantity for individual items. The query should be able to return either stock with one or more items (current stock) or items that have zero stock (say for re-order) or a combination of both to show the full picture.

    The user will be presented with the choice, but there needs to be a choice to enable the view of the full picture.

    All items
    Item# Qty Sold Balance
    Item1 10 5 5
    Item2 7 7 0
    Item3 20 15 5
    Item4 50 5 45
    Item5 10 10 0
    User enters 0
    Item# Qty Sold Balance
    Item2 7 7 0
    Item5 10 10 0
    User enters 1
    Item# Qty Sold Balance
    Item1 10 5 5
    Item3 20 15 5
    Item4 50 5 45
    When the user enters *, however, the query is supposed to return all records, but instead shows none. The results will be shown on a report already constructed. Is this any clearer?
    .
    PC - Ubuntu 15.04 64bit Desktop
    HP Pav G60-236US 3GB RAM Laptop, Ubuntu 15.04 64bit and Win7 Home

    "So Daddy, how come you telling me stealing not right when YOU copying DVDs? How come? How Come?"


    RIP Ramesh ...

  4. #4
    Join Date
    Jan 2007
    Posts
    164
    Rep Power
    18

  5. #5
    Join Date
    Nov 2002
    Posts
    5,713
    Rep Power
    0

    Default

    Did you use a where clause in your query?
    .:] ^ [:.
    .:] Game IDs: xfire_ropy | BC:BC2_ropy | BC: 2142_ropy29 | BF3_ | steam_ropy09 | LoL_ropy09 | Origin_ropy29 [:.

  6. #6
    Join Date
    Aug 2002
    Posts
    6,223
    Rep Power
    0

    Default

    Quote Originally Posted by Ropy View Post
    Did you use a where clause in your query?
    In design view, I put the following as criteria:
    [In stock only? 1=YES, 0=ZERO ONLY, *=ALL]
    This effectively gives a an input box asking for the above.
    .
    PC - Ubuntu 15.04 64bit Desktop
    HP Pav G60-236US 3GB RAM Laptop, Ubuntu 15.04 64bit and Win7 Home

    "So Daddy, how come you telling me stealing not right when YOU copying DVDs? How come? How Come?"


    RIP Ramesh ...

  7. #7
    Join Date
    Nov 2002
    Posts
    5,713
    Rep Power
    0

    Default

    Sorry, I totally forgot about this thread. Were you able to resolve this issue?

    If not, and if there's a query view, try executing the query on the DB and see what happens.
    .:] ^ [:.
    .:] Game IDs: xfire_ropy | BC:BC2_ropy | BC: 2142_ropy29 | BF3_ | steam_ropy09 | LoL_ropy09 | Origin_ropy29 [:.

  8. #8
    Join Date
    Aug 2002
    Posts
    6,223
    Rep Power
    0

    Default

    Quote Originally Posted by Ropy View Post
    Sorry, I totally forgot about this thread. Were you able to resolve this issue?

    If not, and if there's a query view, try executing the query on the DB and see what happens.
    That's where the query is running. I've not yet made an app for the database yet.
    My temporary fix was to create two reports, the one already mentioned and another for the ALL records.
    An alternative that I thought of later was to use code on the report to change the query, but that's a project for later.
    .
    PC - Ubuntu 15.04 64bit Desktop
    HP Pav G60-236US 3GB RAM Laptop, Ubuntu 15.04 64bit and Win7 Home

    "So Daddy, how come you telling me stealing not right when YOU copying DVDs? How come? How Come?"


    RIP Ramesh ...

Posting Permissions

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