Trouble with a query

Results 1 to 2 of 2

Thread: Trouble with a query

  1. #1
    Join Date
    Dec 1969

    Default Trouble with a query

    I am trying to write an app and I ALMOST have it working the way I need it to except for one thing. The database is access with 2 tables, categories and qanda. I need to display all of the categories and the number of completed tasks in each. That part works fine. The part I&#039;m having trouble with is if the category has no completed tasks I need to display 0 for that category. I have run through a number of if/then type statements with the select criteria based on the field "completed" being y for yes I can&#039;t seem to phrase this the way I need. Any thoughts woul;d be appreciated.<BR><BR>Here is the code:<BR><BR>dim conn<BR><BR>set conn = server.createobject("adodb.connection")<BR>conn.op en "DSN=rapose;UID=sa;"<BR><BR>set rs = server.createobject("adodb.recordset")<BR><BR>SQL = "SELECT, categories.catname, count(qanda.catid) as catcount, qanda.complete " _ <BR>& "FROM categories, qanda " _ <BR>& "WHERE = qanda.catid AND qanda.complete = &#039;y&#039; " _ <BR>& "GROUP BY, categories.catname, qanda.complete " <BR> <BR>set rs = conn.Execute(SQL)<BR>Do while not rs.eof<BR>catcount = rs("catcount")<BR>catname = rs("catname")<BR>id = rs("id")<BR>response.write "<BR>&#060;b&#062;&#060;font size=2 face=Arial&#062;&#060;a href=viewall.asp?id="&id&"&#062;" & catname & " has " & catcount & " posts.&#060;/a&#062;"<BR><BR>RS.MoveNext<BR>Loop<BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: Trouble with a query

    Two solutions:<BR><BR>Use nZ in the Access query (on the Access side of things) to convert nulls to zeroes. I&#039;ll let you go read up on that nifty little function.<BR><BR>Alternatively, write your own nZ equivalent and use it server side. I did and it works well.<BR><BR>Write a function that checks for a null, empty variable or empty string, and if it is then return somethign else.<BR><BR>My function call looks like this:<BR><BR>MyVariable = nZ(MyVariable, "replace with this")<BR><BR>It works so well, I&#039;d never be without it.

Posting Permissions

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