Greater Than Equal To Criteria for Query Not Working!

[jimbobjumbalia]

Hello,

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!

[NeoPa]

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.

[jimbobjumbalia]

{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!

[NeoPa]

JimBobJumbalia:
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?

[jimbobjumbalia]

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!

[Luuk]

Pleas show the SQL you have, it is something like:

SELECT employees.[EmployeeNumber], employees.[BaseSalary], employees.[YearsOfService], employees.[VirtualEquity]
FROM employees
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'

[jimbobjumbalia]

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:

>="10"

So when I changed it, it fixed the problem!

[PhilOfWalton]

I rather doubt it.
If you type

? "9" >= "10"
 

In the immediate window, you will get True.

Why isn't YearsOfService Numeric?

Phil

[Luuk]

I bet jimbobjumbalia did change the column to Numeric ! ...

[NeoPa]

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.

JimBobJumbalia:
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.