Results 1 to 14 of 14
  1. #1
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Non-Null Field Combination (2003)

    I'm not sure what your query is trying to do, but here's the SQL of a query that will produce the result you're after:

    SELECT Count(Previsioni.Giorno) AS CountOfGiorno
    FROM Previsioni
    GROUP BY Previsioni.[1kWh], Previsioni.[2kWh]
    HAVING (((Previsioni.[1kWh]) Is Not Null) AND ((Previsioni.[2kWh]) Is Not Null));

    If you want to incorporate the result into another query, use dcount:
    Dcount("Giorno","Previsioni","Previsioni.[1kWh] is not null and Previsioni.[2kWh] is not null")
    Waggers
    If at first you do succeed, you've probably missed something.

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Non-Null Field Combination (2003)

    I'd like to count the combination of the number of records for which the 1kWh and 2kWh fields in the Previsioni table of the attached mdb are not Null. If you select October 3, 2005 in the calendar on the startup form and then launch the Query1a query, you get the message "At most one record can be returned by this subquery". How do you count the number of records in this case?

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Non-Null Field Combination (2003)

    Thank you Dave, I've used your second suggestion and solved it as you can see in the attached mdb.
    I wonder why the ore_f column in the Query1a query displays its content left-aligned. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
    On second thought I think it must be because the DCount function returns a string.

  4. #4
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Non-Null Field Combination (2003)

    That's interesting - DCount should return an integer. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    If you'd like to make sure the query recognises the field as a number, right-click on the field in design view and change the Format property. ("General Number" should do nicely in this instance).
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Non-Null Field Combination (2003)

    Do you think the same philosophy works within a report?
    I'm using the expression
    =DCount("1kWh","previsioni","Giorno=#" & Format([giorno],"mm-dd-yyyy") & "#")
    as control source for the ORE_MARC text box in the attached RVaprio report but I get #Error if, on the startup form, you select October 3, 2005 and click the "Report preview" button.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Non-Null Field Combination (2003)

    1) You must enclose the field name 1kWh in square brackets [ ] since it starts with a number (without the brackets, SQL gets confused)
    2) US date format is mm/dd/yyyy, not mm-dd-yyyy.
    So the expression should be

    <code>=DCount("[1kWh]","previsioni","Giorno=#" & Format([Giorno],"mm/dd/yyyy") & "#")</code>

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Non-Null Field Combination (2003)

    Another thing I did not know, your knowledge of Access is amazing. Thank you Hans.

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Non-Null Field Combination (2003)

    Is there a way to count null values in a field?
    I'm trying to use the expression
    a = DCount("[1kWh]", "previsioni", "Giorno=#" & Format([GIORNO], "mm/dd/yyyy") & "# " & _
    "and [1kWh] is null")
    in the Unload event of the startup form of the attached mdb and, if I select October 3, 2005 on the calendar of the startup form and click the "Set Date" button and then close the form, I was expecting the a variable to have a value of 2 but instead it remains at 0.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Non-Null Field Combination (2003)

    If you specify a field name as first argument of DCount (or any other domain aggregate function), it will only include records for which that field is non-null. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    To include nulls, use "*", this will include ALL records that satisfy the WhereCondition.
    <code>
    a = DCount("*", "previsioni", "Giorno=#" & Format([GIORNO], "mm/dd/yyyy") & "# " & _
    "and [1kWh] is null")</code>

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Non-Null Field Combination (2003)

    Thank you Hans,
    in the same Form_Unload sub, what is wrong with the expression:
    <font color=448800>rst.Filter = "Giorno=" & [Giorno] & " and oref is not null"</font color=448800>
    which, when the sub is invoked, gives the Run-time error 3001:
    The arguments are not of a valid type, are not included in the allowed interval or are in conflict.
    ?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Non-Null Field Combination (2003)

    The only operators allowed in the Filter string in ADO are <, >, <=, >=, <>, = and LIKE. Is Null and Is Not Null are not allowed.
    Instead of opening a recordset on the table and then filtering it, you could construct an SQL string that selects the records you want and open a recordset on that SQL string.

    (Note: as you should know by now, you haven't specified the date correctly. But this is not the cause of the error message).

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Non-Null Field Combination (2003)

    Thank you Hans,
    following your advice, I've substituted the SQL string of Query9 and Query10(please see attachment) for the Source argument of the rst recordset in the Unload event but still rst.RecordCount relevant to October 3, 2005 is zero while it should be 1 because the CODF field in the vap1 or vap2 tables has a value.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Non-Null Field Combination (2003)

    Giorgio,

    I already told you that you're not handling the date correctly. Please take a careful look. You omitted the # characters.

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Non-Null Field Combination (2003)

    <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> the crosshatch!!
    Sorry Hans <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

Posting Permissions

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