Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!
  1. Guest ji, please consider donating today!
      Become a Supporter    ::   Make a Contribution   
    Monthly Recurring Target: $300 :: Achieved: $95

Filtering out values!!

Discussion in 'Information Technology' started by Kwgame, Jul 28, 2006.

  1. Kwgame

    Kwgame
    Expand Collapse
    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?
     
  2. Loading...


  3. SusanV

    SusanV
    Expand Collapse
    Guest

    Hi Kw,

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

    SELECT * FROM YourTable WHERE YourField > 0.00


    --
    hth,
    SusanV

    "Kwgame" <Kwgame@discussions.microsoft.com> wrote in message
    news:5A199163-A695-4C0F-A366-F641629C1E45@microsoft.com...
    >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?
     
  4. Jerry Whittle

    Jerry Whittle
    Expand Collapse
    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
    --
    Jerry Whittle
    Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


    "Kwgame" wrote:

    > 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?
     
  5. Kwgame

    Kwgame
    Expand Collapse
    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?

    "SusanV" wrote:

    > Hi Kw,
    >
    > It would be helpful to see your SQL, but anyway, perhaps this will help. Try
    >
    > SELECT * FROM YourTable WHERE YourField > 0.00
    >
    >
    > --
    > hth,
    > SusanV
    >
    > "Kwgame" <Kwgame@discussions.microsoft.com> wrote in message
    > news:5A199163-A695-4C0F-A366-F641629C1E45@microsoft.com...
    > >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?

    >
    >
    >
     
  6. Kwgame

    Kwgame
    Expand Collapse
    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));


    "Jerry Whittle" wrote:

    > 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.
    >
    >
    > "Kwgame" wrote:
    >
    > > 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?
     
  7. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Wed, 12 Jul 2006 12:50:02 -0700, Kwgame
    <Kwgame@discussions.microsoft.com> wrote:

    >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]
     
  8. Kwgame

    Kwgame
    Expand Collapse
    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.

    "John Vinson" wrote:

    > On Wed, 12 Jul 2006 12:50:02 -0700, Kwgame
    > <Kwgame@discussions.microsoft.com> wrote:
    >
    > >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]
    >
     
  9. Kwgame

    Kwgame
    Expand Collapse
    Guest

    Oh by the way, Design Hours is filled with numbers

    "John Vinson" wrote:

    > On Wed, 12 Jul 2006 12:50:02 -0700, Kwgame
    > <Kwgame@discussions.microsoft.com> wrote:
    >
    > >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]
    >
     
  10. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Thu, 13 Jul 2006 06:16:02 -0700, Kwgame
    <Kwgame@discussions.microsoft.com> wrote:

    >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]
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page