2 fields greater than less than query

[jam]

I have a table.
BackupTbl

The table has 2 fields I want to compare.
Limit
Usage

I obviously want to know when they are over their limit. Tried using expressions but I either return all results or none.
I tried a bit of SQL
SELECT * From BackupTbl
WHERE Limit < Usage
This returns all fields.

:banghead::banghead::banghead:

 

[Mile-O]

Might be a silly question, but are Limit and Usage Numeric or Text fields?

 

[jam]

Both numeric. 2 decimal places.

 

[Mihail]

Create a new field in your query: ShowRecord: ([Limit]<[Usage])
In Criteria put True. This field can be invisible.

 

[Mile-O]

Both numeric. 2 decimal places.

Aside, make sure if using decimal places in numeric fields that it's either a Single or Double type, rather than Long Integer. Despite Access giving you the option to have decimal places, it won't count them in this format.

What sort of typical values are you comparing in Limit and Usage? Could it be as minute as decimal places, as per my first paragraph?

 

[jam]

I've got it set to double as it kept rounding up.

Typical digits are 100.00Limit 87.56Usage

Thanks

 

[Mile-O]

The table has 2 fields I want to compare.

I tried a bit of SQL
SELECT * From BackupTbl
WHERE Limit < Usage
This returns all fields.

Hold on, we may have misread you...you only want to return all rows that meet the criteria, but only the two fields mentioned?

Then rather than using the * wildcard, you need to specify the fields.

SELECT Limit, Usage
FROM BackupTbl
WHERE Limit < Usage;

Or do you mean it returns all rows rather than applying the criteria and you've got fields and rows mixed up?