Results 1 to 8 of 8
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    count null record in fileds (2000 sp 1)

    i want to count all records null or len=0 in ANAGRAFICA1 in field APPELLATIVI but have error...???

    piece of my conn:
    Set DB1 = DBEngine.OpenDatabase("F:ANAGRAFICA.mdb")
    Set RSD1 = DB1.OpenRecordset("ANAGRAFICA1")

    Set RSD1 = DB1.OpenRecordset("SELECT COUNT(*) AS CAMPO_VUOTO FROM ANAGRAFICA1 WHERE APPELLATIVI IS NULL")

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

    Re: count null record in fileds (2000 sp 1)

    What is the error message?

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

    Re: count null record in fileds (2000 sp 1)

    Sal informed me that the problem has been solved (the field name was incorrect).

  4. #4
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count null record in fileds (2000 sp 1)

    Hans solved with code above but 2 dubt:
    1) have in db 1.202.584 rds and the query to count is very slow.
    suggestion to speed up?

    2) ho to add a second condition in query "len=0"
    tks.

    Note:
    Happy to see the Lounge and our friends.

    Set DB1 = DBEngine.OpenDatabase("D:PUBBLICAAPPLICAZIONIANAGR AFICA_OK_10092007.mdb")
    Set RSD1 = DB1.OpenRecordset("ANAGRAFICA1")

    strsql = "SELECT COUNT(*) AS CAMPO_VUOTO FROM ANAGRAFICA1 WHERE APPELLATIVO IS NULL"
    Set RSD1 = DB1.OpenRecordset(strsql)
    CONTA_VUOTO = RSD1.Fields(0)

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

    Re: count null record in fileds (2000 sp 1)

    Remove the line
    <pre>Set RSD1 = DB1.OpenRecordset("ANAGRAFICA1")
    </pre>

    It is not needed since you are opening the recordset again in Set RSD1 = DB1.OpenRecordset(strsql)

    Try changing the SQL to
    <pre>strsql = "SELECT COUNT(*) AS CAMPO_VUOTO FROM ANAGRAFICA1 WHERE Len(APPELLATIVO & '')=0"
    </pre>

    (Note the use of two single quotes within the string)

  6. #6
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count null record in fileds (2000 sp 1)

    Good!
    But you query include also IS NULL condition?

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

    Re: count null record in fileds (2000 sp 1)

    Yes. The expression APPELLATIVO & '' results in an empty string (length = 0) if APPELLATIVO is null.

  8. #8
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count null record in fileds (2000 sp 1)

    Good explain!
    Tks.

Posting Permissions

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