Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find a number between two numbers

    Hi all
    I have a warehouse access Database it has the Contents in this box and the locations of the box. My Contents will contain charts between 500-88400 this box will be located on B3-28A, my next box will have 88600-135900 it will be located in L5-06B. Is there a way that an access Query would let me know that chart 900 is located in B3-28A and that chart # 89700 could be found in L5-06B? Please help I have over 2000 boxes placed all over the warehouse. I'm using Access 2003

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    In order to provide detailed assistance, we need to know our table structure. Do you have a table of charts, and a table of boxes, and a table that links them, or is your table structure different than that? Also, does the position of a chart change over time?
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Dec 2002
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The table structure is the contents in box and the locations of the box is all in one table. The table fields are- Date sent to warehouse, Contents of what is in your box, Location of the shelf the box is in at the warehouse, and date that box can be distroyed. The contents in a box or that box location at the warehouse will never change. So the charts between 500-88400 will be located on shelf B3-28A until the distruction date. Thanks for helping me

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Buckshot,

    Here's a user defined function (UDF) that you can call to determine if the number you are looking for is in the range of the contents of the box.
    Code:
    Public Function bBetween(zContents As String, lNumToFind As Long) As Boolean
    
         Dim vNums     As Variant
         
         vNums = Split(zContents, "-")
         
         bBetween = IIf(lNumToFind >= vNums(0) And lNumToFind <= vNums(1), True, False)
         
    End Function
    Table
    TestData.JPG
    Query Definition
    qryFindChart.JPG
    Prompt for chart no
    Prompt.JPG
    Query Results
    qryFindChartResults.JPG
    Last edited by RetiredGeek; 2012-12-16 at 10:10.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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