Numbers Between

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

Have a table with many entries like this "5700-6700"
or "5900-7000" in particular field. I get a number
like "6001". I want to query out all the records
that "6001" falls between. When I enter in "6001" then I
want to pull up all records with "5700-9999" or "5900-
7000". Is this even possible?

Thanks for any help.
 
If it is possible, consider (on the next revision of the application) to
break the fields into 2 fields, one as the LowNumber and the other as the
HighNumber. This would make it easier to find your between numbers.

Provided the format remains the same (as in ###-###), you can use the
Split() function (with the dash as the delimiter), to pull apart the string
into 2 entities. Convert the entities into numbers and then test for the
"between".

HTH

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
"Rob" said:
Have a table with many entries like this "5700-6700"
or "5900-7000" in particular field. I get a number
like "6001". I want to query out all the records
that "6001" falls between. When I enter in "6001" then I
want to pull up all records with "5700-9999" or "5900-
7000". Is this even possible?

Thanks for any help.
Click to expand...

Rob

The way that I would do this is to create short VBA function that parses the
entry, such as "5700-6700" into a lower and upper boundary, and then checks if
the value is between them. A rough function looks like:

Public Function fValueBetween(strRange As String, intValue As Integer) As
Boolean
On Error GoTo E_Handle
Dim intLower As Integer
Dim intUpper As Integer
If InStr(strRange, "-") = 0 Then
fValueBetween = False
Else
intLower = Left(strRange, InStr(strRange, "-") - 1)
intUpper = Mid(strRange, InStr(strRange, "-") + 1)
If (intValue >= intLower) And (intValue <= intUpper) Then
fValueBetween = True
Else
fValueBetween = False
End If
End If
fExit:
On Error Resume Next
Exit Function
E_Handle:
MsgBox Err.Description & vbCrLf & "fValueBetween", vbOKOnly + vbCritical,
"Error: " & Err.Number
Resume fExit
End Function

You can then use this function in the querty to filter records.
 
Back
Top