Results 1 to 5 of 5

Thread: 2 Count issues

  1. #1
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92

    Question Unanswered: 2 Count issues

    I've searched around, but been unable to determine why my count(iif's haven't been working. Hopefully you can help me out..

    Table: Inventory Columns: A, B, Item_No, QTY, Location, F, G, H, I, J,

    Normally we have 1-10 items in each location, and a row is shown for each item in each location.

    I have a form that takes the current inventory location and adds or subtracts 1 from it to determine the inventory location next to it. (Front and back positions)This new location is in form: Sheet1, Text31.

    On this same form, I'm trying to determine how many items are in the location next to the current one so I have tried to use:

    =Count(IIf([Location]=[Text31],0))

    I keep getting a #error with this, and am not sure why.


    I also need to find out how many unique items are in the current position, but am not sure exactly how to format that one. I guess it would be similar to: Count # of different items in Item_No if the location matches Sheet1, Text31.

    Any assistance would be appreciated.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Would the following query be of any use?
    Code:
    SELECT location
         , Count(*) As [count]
    FROM   inventory
    GROUP
        BY location
    And the problem with your iif is that it is missing a required parameter.
    I.e.
    Code:
    =Count(IIf([Location]=[Text31],1,0))
    Oh, and rename that textbox!
    George
    Home | Blog

  3. #3
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    Geo - I knew that count(*) would come up again
    Dale Houston, TX

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hehe...









    ....git
    George
    Home | Blog

  5. #5
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    georgev,
    Thanks. I've tried the revised IIf statement, but that does not work in the form also. It works in a report, but not in a form.
    I'll try the query and let you know.

    Oh, and the sheet and text names will definitely be getting changed

Posting Permissions

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