Welcome to SPN

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

Sign Up Now!

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]
     

Share This Page