Query to show only values that do not end in .00

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to filter my query to show only the values that do NOT end in
..00. I've tried Not Like ("*.00") and other variations of that, but it
doesn't work. I have a feeling it's possibly because the format is a number
and not text, but I'm not sure how to fix it. Can anyone help?
 
have you tried a where clause that looks something like:

WHERE [yourField] = INT([yourField])

This might not work because of decimal rounding. Another technique might be:

WHERE Format([yourField], "##0.00") Like "*.00"

HTH
Dale
 
WHERE CLng(SomeField) <> Somefield

That will error with any nulls, so you might need

WHERE IIF(SomeField is Null, 0, SomeField) <> CLng(SomeField)



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
have you tried a where clause that looks something like:

WHERE [yourField] = INT([yourField])

This might not work because of decimal rounding. Another technique might be:

WHERE Format([yourField], "##0.00") Like "*.00"
Click to expand...

Format() also causes the value to be rounded (0.5 always rounds away
from zero) e.g.

SELECT FORMAT(12.345, '0.00'), FORMAT(-12.345, '0.00')

returns 12.35 and -12.35 respectively.

Jamie.

--
 
WHERE CLng(SomeField) <> Somefield

That will error with any nulls, so you might need

WHERE IIF(SomeField is Null, 0, SomeField) <> CLng(SomeField)
Click to expand...

I don't think that is correct: the value 123.004 does not end in .00,
yet it gets retained by your WHERE clause.

Assming the values are DECIMAL, I'd suggest:

WHERE SomeField - INT(SomeField) = 0.00

Jamie.

--
 
One, what is your solution?

Two, I did assume that the user would want to retain all numbers that were
not integer values. So if the user wants to drop numbers that end in .00051
then the expression would need to be modified.

Fix(SomeField*100) Mod 100 <> 0 might work if we wanted to exclude numbers
where the first two decimal places are zeroes.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
what is your solution?
Click to expand...

I don't offer solutions in newsgroups but I did offer a suggestion in
my original reply, immediately following the words, "I'd suggest".
Here's a repost:

WHERE SomeField - INT(SomeField) = 0.00

Jamie.

--
 
I'm pretty new to this Access stuff, but the solution:
WHERE CLng(SomeField) <> Somefield did what I needed it to because I would
never have more than 2 decimal places as the field I am using only allows the
user to enter 2 decimal places.

Thanks for all the help!
 
I'm pretty new to this Access stuff, but the solution:
WHERE CLng(SomeField) <> Somefield did what I needed it to because I would
never have more than 2 decimalplaces as the field I am using only allows the
user to enter 2 decimalplaces.
Click to expand...

With respect, I think your spec writing needs some work. You
originally specified "show only the values that do NOT end in .00" and
only later revealed "the field I am using only allows the user to
enter 2 decimal places." Based on this later information, yours must
be a field of type DECIMAL with a scale of 2 -- because all other
numeric types that allow non-integer values (Single, Double and
Currency) allow at least four decimal places -- but can you confirm
this is the case? And why not simply specify "integer value" or "whole
number" or similar?

Jamie.

--
 
As I said in my previous post, I am new to writing queries. I was recently
given security rights to create queries. I post here to learn how to do
things. I'm sorry if I wasn't clear. It wasn't because I was purposefully
trying to mislead people, but rather because I am new to this. It is much
easier to learn from experienced people in these discussion groups than from
a text book.
 
I am new to writing queries.
Click to expand...

But are you new to writing specifications?
It is much
easier to learn from experienced people in these discussion groups than from
a text book.
Click to expand...

Try *answering* a few questions and you may get an impression as to
how frustrating it is to be drip fed information ;-)

Jamie.

--
 
Back
Top