Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2004
    Posts
    21

    Question Unanswered: Pulling null or not null values in a parameter query

    I am using a parameter query that gets its parameters off of a form. I have a combo box and I need to select null or not null as the parameter. However, when I do this it does not work. Does anyone have any ideas or suggestions?

    Thanks in advance,
    Dante

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    strSQL = "SELECT * FROM thisTable WHERE myField IS "
    if not isnull(thisFormControl) then strSQL = strSQL & "NOT "
    strSQL = strSQL & "NULL ;"
    and save to querydef.sql (or .execute it if it's an action query)

    you could probably also get there in query design grid with criteria:
    iif(isnull(thisFormControl), "IS NULL", "IS NOT NULL")

    obviously you could replace
    isnull(thisFormControl)
    in either expression with a checkbox or whatever you like.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Feb 2004
    Posts
    21
    awesome thanks

    Dante

  4. #4
    Join Date
    Feb 2004
    Posts
    21
    anyway to do the following in design view of a query?

    If it returns a null value it just doesnt show anything. Instead get it to return 0. I havent found how to do this in the query design view.

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    nz(myField, 0)
    returns 0 or non-null value

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Feb 2004
    Posts
    21

    Question

    ok, maybe I am going about this the wrong way. Here is my query:

    SELECT [qry LEASED MHP ESC LESS].[P-CODE ASSIGNED], Count(*) AS [TOTALS LEASED_MHP_ESC_LESS]
    FROM [qry LEASED MHP ESC LESS]
    GROUP BY [qry LEASED MHP ESC LESS].[P-CODE ASSIGNED];


    This is usually a very low number that it returns or occasionally wont find any which is fine. But, when it doesnt find any I need it to report as 0 so that I can at least count it. I have tried the nz( , ) option and also IIF(IsNull([MYFIELD]),0,[MYFIELD]) but still shows nothing if it finds nothing. Any ideas or suggestions would be appreciated.

  7. #7
    Join Date
    Feb 2004
    Posts
    21
    I noticed that someone else is having pretty close to the same problem as I. So if I query and it returns no records I need it to count as 0 records. Because later I am pulling all the counted records into another query to provide the values for a report.

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740


    you [GROUP BY P-CODE ASSIGNED] in [qry LEASED MHP ESC LESS]
    and you Count(*) in the same [qry LEASED MHP ESC LESS]

    how can you get a NULL count ??

    if there is no record with [GROUP BY P-CODE ASSIGNED] = "abc", you will not be grouping/counting for "abc" so you have no record returned for "abc".

    if there is any non-zero number of records "abc" then you will count them and get a non-NULL result.

    ...or have i had too many beers?

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Feb 2004
    Posts
    21

    Unhappy

    EDIT: the numbers below in the example arent lining up how they should.


    Ok, I have multiple queries set up the same way. I have a query that finds all the P-CODES ASSIGNED that meet a set criteria. Then I have a seperate query that counts them. I have multiple queries set up this way to count different P-codes with different criteria. I have been able to pull all the "Count" fields from all those queries into one query. Example:

    MHP_ESC | TTP_ESC | MHP_SR | MHP_SR | MHP_SR_LESS | MHP_ESC_LESS
    37 24 190 123 68 0

    Since that last one would technically not return any records it cant count anything and there would be no 0(also since there are no records for that last field it wont show any of the other values like 37,24, 190, etc). So if it returns 0 records I need it to show as 0 in the query that pulls all the data together.

    Lol, I think I am the one that needs the beers I appreciate all your help with this.

    Dante
    Last edited by darkdante; 12-19-04 at 12:49.

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    sorry - got to talking and forgot to press the submit button so we are a bit out of sequence.

    yes - exactly! it looks like your problem is not a NULL count(*) but zero records returned... you can see some discussion of this at (the currently adjacent post)

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Feb 2004
    Posts
    21
    I read that and tried the dcount with no luck. I am not sure how in design view of the query or SQL view on how to get it to return a 0. Since I have a query that pulls all the counts together in one query how can I get it to just show 0 if no records were returned? What would I put in the design view?

  12. #12
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    difficut to say what's going wrong without seeing more of the picture - you probably just need a couple of lines from an SQL guru to set everything straight.

    i'm not the guru you need, but at a guess it seems like you might need to force some NULLs into existance so you can handle them. play with some JOINs from the original data to the Count(*) query and try to trigger some nulls for those zero-record cases ...then handle the NULLs with isnull() or nz() to get the zero you are looking for.

    good luck with it!

    izy
    currently using SS 2008R2

  13. #13
    Join Date
    Feb 2004
    Posts
    21
    Thanks izyrider. I will try to figure it out from here. Thanks for you help Maybe I will mistakenly get it to work like I usually do :P

Posting Permissions

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