Greater Than Criteria Not Working

[lacey]

Hi all.

My DB: View attachment 40106

I can't seem to get the queries to all work the same. Tier 3 and 4 queries work fine for the "greater than or equal to" criteria, but I can't make the exact same command work for Tiers 1 and 2. Any idea why it work work? They pull up all records instead of those equal to or greater than 112.

***Edit 1***
The only difference I can see between the two queries is that the criteria in Tiers 1 and 2 is greater than 100, whereas the other two are below (80 and 56). Does the criteria have to be written differently for values above 100?
Thanks.

 

[plog]

Its because the fields in your TierTotals table are text. If you want numeric data to behave like numeric data, you must store it that way.

As text '90' is greater than '10' because '9' is alphabetically after '1'. So far so good. However, as text that also means '90' is greater than '100' or '1000' or any other number that begins with a '1'.

So if you want to do mathematical operations on your data, use numbers.

 

[lacey]

Got it, thanks!