How to enter data 'ranges' in tables ...?

[Captain Database ...!!]

Does Access recognize ranges and/or greater than/less than figures in the format of, say, 1 - 10 or < 10 ? I've been trying to import an Excel table with data in this format, but everytime I do I get an error message (and the fields where my data is so-entered are left blank).

[boblarson]

You would need to import it to a text field. And what is probably happening is Access checks to see what the first few rows of data contains (it doesn't look at anything beyond that) so it is best if you can sort it to include such or include a bogus row at the first record and then delete it when done importing.

[Captain Database ...!!]

I've tried leaving a few fields blank, except for the primary keys, as suggested although I keep getting type conversion errors; both when importing the Excel table into a 'new' Access table, and when importing it into a pre-designed table with fructification field set to text. In both cases, I get type conversion errors for all fructification levels other than 0. Is there some way to simply copy-in the column (where I've tried, it only pastes the last number of the Excel columns to the selected Access table-cell) ? The midpoint of my Excel table can be seen here (other rows removed for taking of the screenshot):

https://www.accessforums.net/attachm...p;d=1309971014

[boblarson]

You can't put blank fields there as that will not be construed as text. There has to be TEXT in the field. Like I said, you are likely going to need to put a bogus row of data at the top with data in EVERY FIELD for the datatype you want. Then it should work.

[Captain Database ...!!]

Okay, it worked .. funny though because, seeing as it was originally a number column, the first few thousand rows on the complete table are single numbers anyways. I guess the Access gods work in mysterious ways ...

Thanks

[boblarson]

It's a known issue and has been for as long as Access has been around. Apparently the resources required to fix it would exceed the Cost/Benefit ratio that Microsoft would experience. But we can always hope that they will fix it in the next version (don't hold your breath or you will pass out ).

[Captain Database ...!!]

Just kiiinda sucks because I assuuume that this makes the column rather useless for mathematical queries .. ? I suppose I could create what was 'essentially' a "find all greater than" query by setting the criteria to <>"names of fields below desired value" .. still that creates a lot more work and the results, I assume, will be no easier to further query.

[boblarson]

For mathematical operations you can select all records that do NOT have the qualifiers. The qualifiers would essentially make mathematical equations meaningless anyway. If you think about it, if an answer is given as > 10 or < 10 what does that mean? If < 10 it could be 9, 8, 7, 6, 5, 4, 3, 2, 1, 0 (or possibly negative). How do you quantify that for a calculation? You can't.

[Captain Database ...!!]

Yah, that's kinda what I figured; just wanted to be sure...

Thanks again,



C A P T A I N
D A T A B A S E
...:::When your data calls out for a HERO:::...

[Captain Database ...!!]

Just curious; the powers-that-be want to associate an integer with each range value for 'some' sort of statistical purpose. They suggested a new column containing the midpoint of each range and yet, while my first inclination was for an IIf statement, this would take an inadmissable (too long) expression of forty-to-fifty IIf clauses. Is there some better way of doing this?

[boblarson]

How about creating a function (in a standard module with a name other than the procedure name):

Function ReturnMidpoint(varInput As Variant) As Long
    Dim strHold As String
    Dim strCase As String
    Dim lng    As Long

    If Len(varInput & vbNullString) > 0 Then
        For lng = 1 To Len(varInput)
            strCase = Mid(varInput, lng, 1)
            Select Case strCase
                Case 0 To 9
                    strHold = strHold & strCase
            End Select
        Next
        If strHold <> varInput Then
            ReturnMidpoint = strHold / 2
        Else
            ReturnMidpoint = varInput
        End If
    Else
        ReturnMidpoint = 0
    End If
End Function

And then just call it from the query:

MyVal: ReturnMidpoint([FieldNameHere])

[Captain Database ...!!]

Not sure why, but while it is working for "< 10"-type fields, it is returning a number in the hundreds-to-thousands for "10 - 20"-type fields.

[boblarson]

Not sure why, but while it is working for "< 10"-type fields, it is returning a number in the hundreds-to-thousands for "10 - 20"-type fields.

That's because I hadn't thought that you had the 10-20 type fields. I had only thought they were going to be < X , > X, >= X or <= X. So I came up with this changed function:

Function ReturnMidpoint(varInput As Variant) As Long
    Dim strHold As String
    Dim strCase As String
    Dim lng    As Long

    If Len(varInput & vbNullString) > 0 Then
        If InStr(1, varInput, "-") > 0 Then
            Dim varSplit As Variant
            varSplit = VBA.Split(varInput, "-")
            ReturnMidpoint = ((varSplit(1) - varSplit(0)) / 2) + varSplit(0)
        Else
            For lng = 1 To Len(varInput)
                strCase = Mid(varInput, lng, 1)
                Select Case strCase
                    Case 0 To 9
                        strHold = strHold & strCase
                End Select
            Next
            If strHold <> varInput Then
                ReturnMidpoint = strHold / 2
            Else
                ReturnMidpoint = varInput
            End If

        End If
    Else
        ReturnMidpoint = 0
    End If
End Function

[Captain Database ...!!]

WoWzERz .. !!

Hee bee a miracle workerrr .. !!

[Captain Database ...!!]

Been away a few days, but just noticed that my numbers with decimals (there are only a few of them) are returning way-off numbers. Otherwise, everything's working fine (non-decimal integers repeated, and mid-points of ranges given, as expected).