Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Posts
    292

    Unanswered: Counting problems....

    I have an SQL which does a count as shown below.. My problem is If my field FLT is empty it puts a 0 in my query. I am using this query to Graph and do not want the 0 to show up in my query.

    Any Ideas how to fix this ?

    SELECT Count(tblInspections.FLT) AS CountOfFLT, Left([FLT],2) AS FaultType
    FROM tblInspections INNER JOIN tblQR ON (tblInspections.strProject = tblQR.strProject) AND (tblInspections.strArea = tblQR.strArea) AND (tblInspections.strReference = tblQR.strReference)
    GROUP BY Left([FLT],2);


    Thanks

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    SELECT Count(tblInspections.FLT) AS CountOfFLT, Left([FLT],2) AS FaultType
    FROM tblInspections INNER JOIN tblQR ON (tblInspections.strProject = tblQR.strProject) AND (tblInspections.strArea = tblQR.strArea) AND (tblInspections.strReference = tblQR.strReference)
    WHERE FLT IS NOT NULL
    GROUP BY Left([FLT],2);

  3. #3
    Join Date
    Nov 2003
    Location
    San Francisco, CA USA
    Posts
    59

    Maybe

    But that solution only shows records where there is a value for FLT. What I understand Tony T to say is that they should show even if it's blank or null but 0 shouldn't display if there's no value.

    Lookup IIF syntax. It's something like IIF(condition; true response; false response).

    Don't remember if they're semi's in between sections, if it's IFF or IIF or whatever. But you should be able to find it in help.

    Assume that my syntax is a bit wonky (you'll need to look it up in Help to make certain you get it right), try this:

    IIF(Nz([FLT]); ""; Left([FLT],2) AS FaultType

    Working from memory... Correct me anyone if you've got a better grasp of this function. Also, I don't use Nz frequently. You may want to look that up as well. It checks both for null conditions and empty fields as I understand it.

    Magee
    Last edited by mageem; 02-04-04 at 01:43.

  4. #4
    Join Date
    Jul 2003
    Posts
    292

    Thanks !

    Teddy,

    The solution you came up with worked great! thank you so much

    mageem,

    What it was doing was, if there was a null in my field it would put a zero in my query, didnt matter how many null fields I had, it would return a zero in the first field followed by the count of what was asked.

    After putting in "WHere FLT isnot null" into the code it didn't return any null field, thus eliminating the zero in my query. ( I think )

    Anyways thanks for the suggestion

    you both been great help!

  5. #5
    Join Date
    Nov 2003
    Location
    San Francisco, CA USA
    Posts
    59

    I see

    Now it makes more sense.

    So are you quite certain you don't want any records to be included in your results where that one field is null? If so, sounds like you're good to go!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •