Filtering out values!!

[Guest]

I have a query and in my value column I want to filter out all the .00s, I
just want to to show data that has a value (above .00), I tried to use the
NOT experssion in the criteria space but for some reason it does not sort the
..00s. out. what can i do?

 

[SusanV]

Hi Kw,

It would be helpful to see your SQL, but anyway, perhaps this will help. Try

SELECT * FROM YourTable WHERE YourField > 0.00

 

[Guest]

Are you talking specifically about .00 or things like 1.00, 2.00, etc.? If
this is a number field, your format could be set to show 2 decimals even for
the number 1.

Let's say that it's a number field and you don't want to show 1.00, 2.00,
ect. ; however, you do want to show things like 1.01. In the criteria for the
"my value column" put something like:

<>CLng([my value column]) And Is Not Null

 

[Guest]

hey susan, here is my SQL

SELECT PTP.[Work Category], PTP.[Work Type], PTP.[User Contact], PTP.[Work
Order], PTP.Activity, PTP.Status, PTP.Address, PTP.TRS, PTP.[Design Hours]
FROM PTP
GROUP BY PTP.[Work Category], PTP.[Work Type], PTP.[User Contact], PTP.[Work
Order], PTP.Activity, PTP.Status, PTP.Address, PTP.TRS, PTP.[Design Hours],
PTP.[Want Date], PTP.City, PTP.[Estimate Units], PTP.[Unit Measure]
HAVING (((PTP.[Design Hours])<>"Design") AND ((Sum(Len(Trim([TRS] &
""))))=0));

where would I place that statement?

 

[Guest]

All I want to do is show values over 0

here is my SQL:

SELECT PTP.[Work Category], PTP.[Work Type], PTP.[User Contact], PTP.[Work
Order], PTP.Activity, PTP.Status, PTP.Address, PTP.TRS, PTP.[Design Hours]
FROM PTP
GROUP BY PTP.[Work Category], PTP.[Work Type], PTP.[User Contact], PTP.[Work
Order], PTP.Activity, PTP.Status, PTP.Address, PTP.TRS, PTP.[Design Hours],
PTP.[Want Date], PTP.City, PTP.[Estimate Units], PTP.[Unit Measure]
HAVING (((PTP.[Design Hours])<>"Design") AND ((Sum(Len(Trim([TRS] &
""))))=0));

Are you talking specifically about .00 or things like 1.00, 2.00, etc.? If
this is a number field, your format could be set to show 2 decimals even for
the number 1.

Let's say that it's a number field and you don't want to show 1.00, 2.00,
ect. ; however, you do want to show things like 1.01. In the criteria for the
"my value column" put something like:

<>CLng([my value column]) And Is Not Null
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a query and in my value column I want to filter out all the .00s, I
just want to to show data that has a value (above .00), I tried to use the
NOT experssion in the criteria space but for some reason it does not sort the
.00s. out. what can i do?

 

[John Vinson]

All I want to do is show values over 0

Values of WHAT over zero? Of the sum? of the individual field values?

Not sure where you got the complex ((Sum(Len(Trim([TRS] &
""))))=0)) gibberish; if you want to see only nonzero values of TRS
then

SELECT PTP.[Work Category], PTP.[Work Type], PTP.[User Contact],
PTP.[Work Order], PTP.Activity, PTP.Status, PTP.Address, PTP.TRS,
PTP.[Design Hours]
FROM PTP
WHERE (((PTP.[Design Hours])<>"Design") AND [TRS] <> 0;

If you want to see records where the Sum of TRS is greater than zero
you do indeed need the Totals query and a Having clause:

SELECT PTP.[Work Category], PTP.[Work Type], PTP.[User Contact],
PTP.[Work Order], PTP.Activity, PTP.Status, PTP.Address, PTP.TRS,
PTP.[Design Hours]
FROM PTP
GROUP BY PTP.[Work Category], PTP.[Work Type], PTP.[User Contact],
PTP.[Work Order], PTP.Activity, PTP.Status, PTP.Address, PTP.TRS,
PTP.[Design Hours], PTP.[Want Date], PTP.City, PTP.[Estimate Units],
PTP.[Unit Measure]
WHERE PTP.[Design Hours]<>"Design"
HAVING Sum([TRS]) <> 0;

Bear in mind I have no way to know how your table is structured, the
datatype of TRS, etc. so this may not be exactly what you need!

I presume that the field named "Design Hours" is just oddly named, and
in fact contains text strings such as "Design" rather than containing
a number of hours...


John W. Vinson[MVP]

 

[Guest]

I am get a raw text file from a server at work and in the text file there are
words and number. I have a field that is called "Design Hours" but in the
text file the word Design Hour is at the top of each section of hours, so i
filtered that out, I didnt want to change the fields in the table from text
to number becuase the numbers aren't as accurate for some reason. Anyway the
((Sum(Len(Trim([TRS] & ""))))=0)) statement is for trapping all the blank
spaces from my table. What I am doing is getting all the black TRS spaces
and getting the Design Hours that are associated with the blank spaces. In
the Design Hours column some values show 0 and I want to show all the spaces
that have a value, so I am trying to filter the 0's out.

All I want to do is show values over 0

Values of WHAT over zero? Of the sum? of the individual field values?

Not sure where you got the complex ((Sum(Len(Trim([TRS] &
""))))=0)) gibberish; if you want to see only nonzero values of TRS
then

SELECT PTP.[Work Category], PTP.[Work Type], PTP.[User Contact],
PTP.[Work Order], PTP.Activity, PTP.Status, PTP.Address, PTP.TRS,
PTP.[Design Hours]
FROM PTP
WHERE (((PTP.[Design Hours])<>"Design") AND [TRS] <> 0;

If you want to see records where the Sum of TRS is greater than zero
you do indeed need the Totals query and a Having clause:

SELECT PTP.[Work Category], PTP.[Work Type], PTP.[User Contact],
PTP.[Work Order], PTP.Activity, PTP.Status, PTP.Address, PTP.TRS,
PTP.[Design Hours]
FROM PTP
GROUP BY PTP.[Work Category], PTP.[Work Type], PTP.[User Contact],
PTP.[Work Order], PTP.Activity, PTP.Status, PTP.Address, PTP.TRS,
PTP.[Design Hours], PTP.[Want Date], PTP.City, PTP.[Estimate Units],
PTP.[Unit Measure]
WHERE PTP.[Design Hours]<>"Design"
HAVING Sum([TRS]) <> 0;

Bear in mind I have no way to know how your table is structured, the
datatype of TRS, etc. so this may not be exactly what you need!

I presume that the field named "Design Hours" is just oddly named, and
in fact contains text strings such as "Design" rather than containing
a number of hours...


John W. Vinson[MVP]

 

[Guest]

Oh by the way, Design Hours is filled with numbers

All I want to do is show values over 0

Values of WHAT over zero? Of the sum? of the individual field values?

Not sure where you got the complex ((Sum(Len(Trim([TRS] &
""))))=0)) gibberish; if you want to see only nonzero values of TRS
then

SELECT PTP.[Work Category], PTP.[Work Type], PTP.[User Contact],
PTP.[Work Order], PTP.Activity, PTP.Status, PTP.Address, PTP.TRS,
PTP.[Design Hours]
FROM PTP
WHERE (((PTP.[Design Hours])<>"Design") AND [TRS] <> 0;

If you want to see records where the Sum of TRS is greater than zero
you do indeed need the Totals query and a Having clause:

SELECT PTP.[Work Category], PTP.[Work Type], PTP.[User Contact],
PTP.[Work Order], PTP.Activity, PTP.Status, PTP.Address, PTP.TRS,
PTP.[Design Hours]
FROM PTP
GROUP BY PTP.[Work Category], PTP.[Work Type], PTP.[User Contact],
PTP.[Work Order], PTP.Activity, PTP.Status, PTP.Address, PTP.TRS,
PTP.[Design Hours], PTP.[Want Date], PTP.City, PTP.[Estimate Units],
PTP.[Unit Measure]
WHERE PTP.[Design Hours]<>"Design"
HAVING Sum([TRS]) <> 0;

Bear in mind I have no way to know how your table is structured, the
datatype of TRS, etc. so this may not be exactly what you need!

I presume that the field named "Design Hours" is just oddly named, and
in fact contains text strings such as "Design" rather than containing
a number of hours...


John W. Vinson[MVP]

 

[John Vinson]

Oh by the way, Design Hours is filled with numbers

In that case, a criterion

[Design Hours] = "Design"

will ensure that NO records are ever retrieved, since the text string
"Design" is not numeric.

John W. Vinson[MVP]