DCount and negative number

[runsilent84]

I am trying to count the number of fields that have a range of negative numbers.
IDF_Log: Table
[Temp (C)]: Temperate range in Celsius(set as text format due to some temps are unknown)
I would like to keep the temperature in Celsius due to all the data but may be forced to convert the data to kelvin so there are no negative numbers.

I have tried the below code and get an "#ERROR" error

=DCOUNT("[ID1]", "IDF_Log", "[Temp (C)] >= -30 AND [Temp (C)] <= -5")

=DCOUNT("[ID1]", "IDF_Log", "[Temp (C)] >= (30) AND [Temp (C)] <= (5)")

returns 32 which is incorrect

=DCOUNT("[ID1]", "IDF_Log", "[Temp (C)] >= '-30' AND [Temp (C)] <= '-5'")

Any insight as to how to depict negative numbers in the criteria is greatly appreciated.

[jimatqsi]

You don't say what is stored when the value of a temperature is unknown. You could try referring to CINT([Temp C]) in order to convert the temperature to an integer - or to CDBL instead of CINT if you need decimal places.

If the unknown temps are stored as some string you could also use iif statements like this:

iif(IsNumeric([Temp C],CINT([Temp C]),[whatever value you want for missing temps])

You can also avoid converting if you simply add some value when storing the number and subtract that same number when reading - okay so that's still a kind of conversion. But what's the point of avoiding negative numbers?

Jim

[runsilent84]

Jim
The unknown values are labeled as "unk" in the table. I believe that my question is misunderstood. I want to count the number of values that are within the range listed above. So for example if I had the following values in the table (-25, -10, 5, 20, -2, -15) the DCount formula would return the number 3.

[Rabbit]

It's not because of negative numbers. It's because you're using text data type to store numbers. So it's trying to convert text to a number but it can't for some of your data so it errors out.

For those that don't have a known temperature, they should be stored as null and the data type of your column should be a number.

[runsilent84]

Rabbit,
Thanks for your help. Changing data type to number fixed my issues.