Trouble calculating null values

Results 1 to 3 of 3

Thread: Trouble calculating null values

  1. #1
    Join Date
    Dec 1969

    Default Trouble calculating null values

    Im using an access database.<BR>Problem I need help with is this.<BR>I have a field called [OUTCOME] in a table. the only text in this field can be... WIN LOSS TIE<BR>i need to do a calculation where a win is worth 5 points, a loss 1 point and tie 3 points.<BR>a cross tab query will count records for win&#039s losses and ties and group by the player, but if a player didn&#039t have any matching reports then it just returns a null value which i can&#039t seem to calculate.<BR>[PLAYER] [WIN] [LOSS] [TIE]<BR>Ron 7<BR>inside access i can do a simple query of this crosstab query and use Nz to sum them up like this<BR>i.e.: Points: Nz([WIN],0)*5+Nz([LOSS],0)*1+Nz([TIE],0)*3<BR>this converts nulls to a 0 so calculations work <BR>BUT<BR>when export from access to .asp and run i get the following error<BR><BR>[Microsoft][ODBC Microsoft Access Driver] Undefined function &#039Nz&#039 in expression<BR><BR>After export from access to .asp this is what the code is like<BR><BR>&#060;%<BR>If IsObject(Session("raff_conn")) Then<BR> Set conn = Session("raff_conn")<BR>Else<BR> Set conn = Server.CreateObject("ADODB.Connection")<BR> "raff","",""<BR> Set Session("raff_conn") = conn<BR>End If<BR>%&#062;<BR>&#060;%<BR>If IsObject(Session("Points2_rs")) Then<BR> Set rs = Session("Points2_rs")<BR>Else<BR> sql = "SELECT [Outcome2].[Tourney], [Outcome2].[Total Of initial], [Outcome2].[WIN], [Outcome2].[LOSS], [Outcome2].[TIE], Nz([WIN],0)*5+Nz([LOSS],0)*1+Nz([TIE],0)*3 AS Expr1 FROM Outcome2 "<BR> Set rs = Server.CreateObject("ADODB.Recordset")<BR> rs.Open sql, conn, 3, 3<BR> If rs.eof Then<BR> rs.AddNew<BR> End If<BR> Set Session("Points2_rs") = rs<BR>End If<BR>Session.Abandon<BR>%&#062;<BR><BR>I have found out just how hard working with nulls really are.<BR>I sure hope someone out there can help out or point me in the right direction.<BR>Thanks all.<BR>Russell<BR>

  2. #2
    Ron Campbell Guest

    Default RE: Trouble calculating null values

    Sorry guy but I don&#039t think it can be done.<BR>Here&#039s why<BR>in the table that holds your field called outcome has records and those records all have existing values which is a choice of 3 items.<BR>It&#039s impossible to have a null value in this field if what i&#039m reading is correct?<BR>The problem you have with nulls comes after you do a crosstab query. The null really doesn&#039t exist in records. <BR>So the real question is how do you give a 0 value to a non existent null value?<BR>I don&#039t think there is a way to do it, but if there is, it would not be effecient and will really slow down the process.<BR>You might want to try a different setup with your db to overcome that problem.<BR>If i&#039m wrong I&#039m sorry and am currious to know the answer too.<BR>GL<BR>

  3. #3
    Join Date
    Dec 1969

    Default RE: Sorry for the imposible

    leave it to me to find the impossible.<BR>ill see if there is some other way i can do this.<BR>thanks ron for you input.<BR>

Posting Permissions

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