Find a number between two numbers

Topic

New Reply

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

Reply | Quote

Replies

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?

Reply | Quote

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

Reply | Quote

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
32623-TestData
Query Definition
32624-qryFindChart
Prompt for chart no
32625-Prompt
Query Results
32626-qryFindChartResults
:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote