I'm looking for a way to retrieve records from an Access db to be displayed in a table.

I will be getting a string or numeric value from the user and converting it input to a single type.

Then I want to search a field called "value" in my db to see if the user input value is between the values in my field.

Then I want to output ONLY those rows/records that meet the filtering criteria.

The field is titled "value" and it contains strings like the following: "10-50k"

I've already written a function that can convert the string data from the value field and the user input into three separate numbers to do the comparison.

THE PROBLEM:

I don't know whether to filter the records before or after creating the recordset.

When I perform the comparison after calling the entire db out, it is easy to make the comparison, but not so easy to accurately monitor the paging.

On the other hand, I don't know how to build the SQL to do a complex conversion/comparison "on the fly", so as to only include filtered records.


ex. user input: =
    4.7k


ex. table=
    id name value size
    1 elf 10-50k 10
    2 joker 50k-100g 50
    3 oscar 50k-200m 14


desired output=
    id name value size
    1 elf 10-50k 10


Thanks,
ja



ja

Well it would be better before from a performance point of view but your filter expression is going to be awkward because your table is non-normalised, the 'value' column has three pieces of data, minimum, maximum and unit type, it would be a lot easier if these were spread over three fields. As you are using Access you will probably need to write a module based function which accepts a field's data and the user input and tests whether it matches. Then if your function were called 'isInRange' your SQL would look like:
--

Joe

Thanks Joe,
I guess normalizing the data should be goal number one. I realized this early on but decided to "tough-it-out" and try to come up with a clever solution.

Per your suggestion, I'll try using my function in the where clause.



ja

Well joe,
It seems so logical to build the sql string as you suggested.

However, now I get "undefined Function" error.

I renamed the field to "range" to eliminate reserved word conflict.

The function captures user input so I don't think i need to include it as a func. param.



ja

What code do you have for your isInRange function?

--

Joe

The code for the function merely reduces the "3" strings to thier numeric form.

It works fine when I use it "after" calling out the entire db using: "If isInRange(fldF.Value) = true Then...display the record"

The problem there is that the other fields in the record get displayed before the function is called. So, if isInRange returns false I have part of a record that I don't wish to see.



ja

Here's the function code:

Function ConvertDbCellValue (varRaw)
'response.Write varRaw
'response.End()
'/////This sub-routine converts the database value field into two numbers
'/////which represent the low end and high end of the value range
Dim iStart, iStrLen, iDashLoc, strUserInput, strRangeLow, strRangeHigh, sngRangeLow, sngRangeHigh, sngUserInput, varUserValMult, varCellValMult
'/////Initialize some variables
    iStart = 1
    iStrLen = Len(varRaw)
'/////Find the hyphen
    iDashLoc = instr(iStart, varRaw, "-")
'/////Return the string before the hyphen
    strRangeLow = Mid(varRaw, iStart, (iDashLoc-1))
'/////Test to see if the last character is numeric
'/////If NOT then find out what it is and multiply the numeric portion
'/////of the string by the appropriate multiplier
    If NOT IsNumeric(right(strRangeLow,1)) Then
            Select Case UCase(right(strRangeLow,1))
            Case "K"
                sngRangeLow = CSng(Mid(strRangeLow, 1, Len(strRangeLow) - 1))
                sngRangeLow = sngRangeLow * 1000
            Case "M"
                sngRangeLow = CSng(Mid(strRangeLow, 1, Len(strRangeLow) - 1))
                sngRangeLow = sngRangeLow * 1000000
            Case "G"
                sngRangeLow = CSng(Mid(strRangeLow, 1, Len(strRangeLow) - 1))
                sngRangeLow = sngRangeLow * 1000000000
            Case "T"
                sngRangeLow = CSng(Mid(strRangeLow, 1, Len(strRangeLow) - 1))
                sngRangeLow = sngRangeLow * 1000000000000
            Case Else
                Response.Write("Contact the <a href='MAILTO:'>webmaster</a>")
        End Select
    Else
'/////If the last character IS numeric then convert the string to SINGLE and set it = to the variable
        sngRangeLow = CSng(strRangeLow)
    End If
'/////SEE NOTES ABOVE
    strRangeHigh = Mid(varRaw, (iDashLoc + 1), (iStrLen - iDashLoc))
        If NOT IsNumeric(right(strRangeHigh,1)) Then
            Select Case UCase(right(strRangeHigh, 1))
            Case "K"
                sngRangeHigh = CSng(Mid(strRangeHigh, 1, Len(strRangeHigh) - 1))
                sngRangeHigh = sngRangeHigh * 1000
            Case "M"
                sngRangeHigh = CSng(Mid(strRangeHigh, 1, Len(strRangeHigh) - 1))
                sngRangeHigh = sngRangeHigh * 1000000
            Case "G"
                sngRangeHigh = CSng(Mid(strRangeHigh, 1, Len(strRangeHigh) - 1))
                sngRangeHigh = sngRangeHigh * 1000000000
            Case "T"
                sngRangeHigh = CSng(Mid(strRangeHigh, 1, Len(strRangeHigh) - 1))
                sngRangeHigh = sngRangeHigh * 1000000000000
            Case Else
                Response.Write("Contact the <a href='mailto:'>webmaster</a>")
        End Select
    Else
        sngRangeHigh = CSng(strRangeHigh)
    End If
'/////////////////////////Convert User Input///////////////////////////////////

    strUserInput = Trim(varUserInputValue)
    If NOT IsNumeric(right(strUserInput,1)) Then
            Select Case UCase(right(strUserInput,1))
            Case "K"
                sngUserInput = CSng(Mid(strUserInput, 1, Len(strUserInput) - 1))
                sngUserInput = sngUserInput * 1000
            Case "M"
                sngUserInput = CSng(Mid(strUserInput, 1, Len(strUserInput) - 1))
                sngUserInput = sngUserInput * 1000000
            Case "G"
                sngUserInput = CSng(Mid(strUserInput, 1, Len(strUserInput) - 1))
                sngUserInput = sngUserInput * 1000000000
            Case "T"
                sngUserInput = CSng(Mid(strUserInput, 1, Len(strUserInput) - 1))
                sngUserInput = sngUserInput * 1000000000000
            Case Else
                Response.Write("Contact the <a href='MAILTO:'>webmaster</a>")
        End Select
    Else
        sngUserInput = CSng(strUserInput)
    End If
    If sngUserInput >= sngRangeLow and sngUserInput <= sngRangeHigh then
        ConvertDbCellValue = true
    Else
        ConvertDbCellValue = false
    End If
strRangeLow = ""
strRangeHigh = ""
strUserInput = ""
sngRangeLow = ""
sngRangeHigh = ""
sngUserInput = ""
End Function


ja

Joe thanks for the advice. I ended up just normalizing the data in the database.
After that it was a piece of cake to compare fields.

I just used CDbl([cell_name]) <= varUserInputValue.

It works like a charm.

Beers on me.

ja

Well I'm in the States in April, unfortunately I'll be in Seattle which is a little far from Rhode Island.



--

Joe