Results 1 to 8 of 8
  1. #1
    barkly is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2011
    Posts
    23

    Change blank to a zero isnull or nz or what?

    My query relates to the summing of monthly interest charges over the period of a financial year – the financial year being between July 1 and June 30.The problem I have is that at the change of financial year as happened recently the query gives a blank until the first interest charge is entered which may not be until August comes by. Instead of a blank I would like to display zero. Within the table as at July there is obviously no entry yet for either an interest charge or a date of the charge. I have tried both IsNull() and Nz() type functions in an endeavour to display a zero but it is not occurring. If anyone can advise what needs to be done much appreciated. Only some amateur Access experience here but more of a hacker.



    Code:
      
    SELECT IIf(IsNull([Val2]),"Interest Charged this Fin Yr","Interest Charged this Fin Yr") AS Text2, Nz(Sum([tbl_Interest].[MonthlyInterest]),0) AS Val2, tbl_Interest.AccountID
      FROM qry_Account INNER JOIN tbl_Interest ON qry_Account.AccountID = tbl_Interest.AccountID
      WHERE (((qry_Account.InUSe)="Yes") AND ((tbl_Interest.DateInterest)>=IIf(Month(Date())>6,DateSerial(Year(Date()),7,1),DateSerial(Year(Date())-1,7,1))))
      GROUP BY tbl_Interest.AccountID, tbl_Interest.InterestType
      HAVING (((tbl_Interest.InterestType)="Charged"));

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    NZ(field,0)

    this converts nulls to zero. But it doesnt convert "", so I made my on function to catch it all.
    usage:
    NZZ(field)

    Code:
    Public Function Nzz(ByVal pvAmt)
    On Error Resume Next
    Select Case True
      Case IsNull(pvAmt)
        Nzz = Val(Nz(pvAmt, 0))
      Case pvAmt = ""
         Nzz = 0
      case else
         Nzz = pvAmt
    End Select
    End Function

  3. #3
    barkly is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2011
    Posts
    23
    Thanks ranman256,

    Your response is for hard code behind a form (as I understand it). My problem is broader than just the one example I used initially where there are other queries covering interest earned, dividends received, and funds applied and funds withdrawn from the portfolio. These queries are then a subset of a parent query where all these various parameters with several others are brought together for eventual display. Thus I was hopeful of overcoming my blank display problem via a query which I could then apply the method to these other parameters

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    The function would go in a general module then it could be called from anywhere.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    barkly is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2011
    Posts
    23
    Thanks June7,
    I observe in the above code that there is the use of the Nz function and presumably will do the job required. Given that my query also wants to make use of the Nz function I would like to ask why will it work in the function and not in my query?

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    If there are no records returned then there is nothing for Nz() to calculate with, not even Null.

    Didn't say custom function would have different result. I only pointed out where it needs to be located if you want to use it.

    Did you try the custom function? Should get same result.

    If you have to show a month even if there is no data, that gets tricky. Join the filtered aggregate query to a dataset of all month names.

    I have a report with 12 textboxes using DSum() calculations so I show every month even when there is no data.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Perhaps make the default value of MonthlyInterest zero in tblInterest.

  8. #8
    barkly is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2011
    Posts
    23
    Thanks June7 and davegri for you responses to my post. And also the other threads.
    As June7 advised -If there are no records returned then there is nothing for Nz() to calculate with, not even Null.
    This is for me my limiting factor - default values also not working - plus the module approach as first advised basically means at my skill level a complete rewrite of this segment of my application so I have not attempted that approach.
    As I have done in prior years I populate the various tables with a current year date and a zero value and that allows all the readouts I am after.
    Again thanks for you interest.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 15
    Last Post: 07-24-2014, 09:45 AM
  2. Replies: 2
    Last Post: 05-20-2014, 09:32 AM
  3. IIF IsNull
    By angie in forum Forms
    Replies: 11
    Last Post: 12-17-2013, 02:25 PM
  4. Update query to change field data to blank
    By xtrareal22 in forum Queries
    Replies: 3
    Last Post: 12-11-2013, 05:17 PM
  5. Replies: 1
    Last Post: 09-05-2008, 12:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums