By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,761 Members | 1,879 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,761 IT Pros & Developers. It's quick & easy.

Greater Than Equal To Criteria for Query Not Working!

P: 4

In the criteria for a query, I try entering >=10 but it keeps showing me everything below and above 10. It's like the criteria I entered isn't changing anything! I am new to access, so this is pretty frustrating. Can someone please help!

I linked what I am talking about here (Refer to YearsOfService):
{Link removed}

Thank you!
Mar 17 '18 #1
Share this Question
Share on Google+
9 Replies

Expert Mod 15k+
P: 31,489
Firstly, please try to follow the instructions on posting a new question. You're new so may have gone straight to posting without checking the instructions first. That's not the best way to approach things.

In this case it's important that you explain fully what you're working on. Is it a numeric field you're filtering? If you need to post pictures then you can attach it to your post. Do not link to external sites for that.

There is a massive difference between >=10 and '>=10'. Without knowing the type of the field you're applying it to it's hard to guess at exactly where you've gone wrong.
Mar 17 '18 #2

P: 4
{Illegal link removed}

That's the link I was talking about...

Yes, it is a numeric field I am trying to filter. What I entered was >=10

So when I go into DataSheet view I can see numbers under 10, (like 9 and 5) as you can see in the link I have attached.

Please help!! Thank you!
Mar 17 '18 #3

Expert Mod 15k+
P: 31,489
as you can see in the link I have attached.
That is not an attached link - that's an external link. Not helpful. I even took my safety in hand and tried to link to it - just to test whethere or not it was safe. I got nothing, so I hope it wasn't a malicious link that ran unwanted code.

The main point is that you don't link off to such sites. Only sites which are about the product (Access) and are not either commercial or provided by the provider of the product (Microsoft).

As for your issue, I'm not sure filters even work in DataSheet view. I very rarely use it myself. Do you also see records where the value is greater than ten or do you only see those less than ten?
Mar 17 '18 #4

P: 4
I apologize for that. And it was not a malicious link, so no need to worry about that.

What I meant was that when I ran the query in DataSheet view it showed me values greater and less than ten, when I set the Criteria to >=10

I am not sure why it isn't working!

I guess I'll explain what it is that I am working on since I cannot send you any pictures of it! So the query has 4 different fields: EmployeeNumber, BaseSalary, YearsOfService and VirtualEquity. VirtualEquity is BaseSalary*YearsOfService derived via a calculated method in the query. And I just need to show all employees who have worked for 10+ years (YearsOfService >=10). Now, this is where I am stuck!

I will appreciate any kind of help!
Mar 18 '18 #5

Expert 100+
P: 1,035
Pleas show the SQL you have, it is something like:
Expand|Select|Wrap|Line Numbers
  1. SELECT employees.[EmployeeNumber], employees.[BaseSalary], employees.[YearsOfService], employees.[VirtualEquity]
  2. FROM employees
  3. WHERE  employees.[YearsOfService] >10;

This is posted using the '[CODE/]' button in the bar above where I type this. In that bar is also a button to insert an immage ....

Or attach an image via 'Go Advanced', 'Manage attachments'

Attached Images
File Type: png 2018-03-18 18_16_02-Microsoft Edge.png (4.9 KB, 920 views)
Mar 18 '18 #6

P: 4
Hello! Thank you for the reply, but I have figured it out. My Data Type was not Number. Automatically added the quotation marks around the 10:


So when I changed it, it fixed the problem!
Mar 18 '18 #7

Expert 100+
P: 1,430
I rather doubt it.
If you type
Expand|Select|Wrap|Line Numbers
  1. ? "9" >= "10"
In the immediate window, you will get True.

Why isn't YearsOfService Numeric?

Mar 18 '18 #8

Expert 100+
P: 1,035
I bet jimbobjumbalia did change the column to Numeric ! ...
Mar 18 '18 #9

Expert Mod 15k+
P: 31,489
Hi JimBob.

You may have gleaned from the other posts here some of the importance of field types with the filter strings. I have an article (Quotes (') and Double-Quotes (") - Where and When to use them) that may help with that understanding. A very important understanding without doubt.

Phil in Post #8:
? "9" >= "10"
Note from this that digits held in strings do not behave the same way as those same digits when held as numeric data. It is important that you convert your filter back to using a numeric value and the matching field to a numeric field. Otherwise you'll fall over more items that seem bewildering, but actually make perfect sense when you understand that it's comparing text values on not their numerical equivalents.

I apologize for that. And it was not a malicious link, so no need to worry about that.
Good to know. As I said in my first response, I suspected it was simply a lack of knowing your way around.

See Luuk's post #6 to see that pictures can be posted and displayed here.
Mar 18 '18 #10

Post your reply

Sign in to post your reply or Sign up for a free account.