Incorrect query results?

[kujito]

MS Access 2007
A table of lithology data includes a 'FROM' field and a 'TO' field, which are measured in meters. These fields refer to an depth interval of a piece of a drill hole core sample. The intervals are measured downward from the surface to the bottom of the hole. The 'FROM' value should be less than the 'TO' value in every record.
For example:
LITHOLOGY_ID | HOLE_ID | ROCK_CODE | FROM | TO

1 | 1 | 3 | 1.52 | 4

Trying to check data correctness as there is at least one entry where the 'FROM' value is greater than the 'TO' value. Due to the large number of records, manually checking the entire table is not reasonable.
My query is:
Select * from LITHOLOGY
Where FROM > TO;

It will run, but the results do not fit the query. In the first record selected, for example, FROM = 59.32 and TO = 108.61. The FROM is not greater than the TO.

Hoping to find a negative number, I tried to run a few versions of:
SELECT LITHOLOGY.LITHOLOGY_ID, LITHOLOGY.HOLE_ID, LITHOLOGY.FROM, LITHOLOGY.TO, SUM(LITHOLOGY.FROM - LITHOLOGY.TO) AS [INVERTED]
FROM LITHOLOGY
WHERE LITHOLOGY.FROM > LITHOLOGY.TO;

which results in error "You tried to execute a query that does not include the expression 'LITHOLOGY_ID' as part of an aggregate function.

Sorry for the length, just trying to make it clear.
Thanks,
Brian

 

[pbaldy]

Using the Sum function is causing this error. You would need a GROUP BY clause if you really want to sum those up. Otherwise, drop the Sum and just subtract the two values. From the sound of the result of your first query, it looks like the values are being treated as text rather than numbers. What are the data types of those 2 fields? If text, either change them to a numeric type or force it like:

Select * from LITHOLOGY
Where Cdbl(FROM) > CDbl(TO)

 

[kujito]

DOH!
They were set as text! Older geologists aren't much help with checking those sorts of things. Thanks!
BTW, 'Cdbl' = convert to double?
Thanks again,
Brian

 

[pbaldy]

Yes, there are a number of conversion function like that; CInt, CDate, etc.