Results 1 to 14 of 14
  1. #1
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283

    Summing Nulls

    I have revenue data that comes in as text. Sometimes the field contains a 0 sometimes it is null when it is not an actual revenue amount.


    I create a table using a query where I am trying to change the text to number so I can add the values together in another query.
    Code:
    Install_Credit: IIf(IsNull([Rental Credit Install Revenue]),0,[Rental Credit Install Revenue])
    Install_TCV: IIf(IsNull([Rental Install TCV]),0,[Rental Install TCV])
    The make table creates these fields as Number Double - which is great

    When I try and add them together I get a datatype mismatch.
    Code:
    Total Install Rental Revenue: Sum([Install_TCV])+Sum([Install_Credit])
    I am assuming it is the 0 that comes in as text but I can't figure out how to handle it

    Thanks in advance

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    Try Sum(CDbl([Install_TCV]))+Sum(CDbl([Install_Credit]))

  3. #3
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    Quote Originally Posted by ArviLaanemets View Post
    Try Sum(CDbl([Install_TCV]))+Sum(CDbl([Install_Credit]))
    I still get the same data mismatch error

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If it's Null, wrap each field in the Nz() function:

    Nz(FieldName, 0)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    Quote Originally Posted by pbaldy View Post
    If it's Null, wrap each field in the Nz() function:

    Nz(FieldName, 0)

    I tried that too and got the same datatype mismatch error. I am not sure why.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You're sure the data type is number in the table? Can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    db.zipI attached it.

    EXEC_SUMM gets imported - everything is text
    STEP_1 is run - everything is still text
    qry_IOPS_Tracker is run - revenue fields are converted to Number
    IOPS_FINAL_TRACKER_CM Is run - datatype mismatch error

    Thanks for looking at this for me

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try deleting the summed fields and see what happens. The error is coming from the Year([YEAR_MONTH_NUM]), as the field isn't a date/time field. Try the Left() function if you want to leave it as text.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    db2.zip

    ok I added an update statement to deal with the YEAR_MONTH_NUM issue but the numbers are still not summing right. If you look in the IOPS Tracker table under Agency/Direct Column for SUPERIOR DIGITAL you will see there is revenue showing in the SumOfRental Credit Order Revenue and SumOfRental Credit Install Revenue Fields but when you run the IOPS_FINAL_TRACKER_CM query - SUPERIOR DIGITAL comes up blank.

    I have readded the db with my changes

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Adding the Nz() function I mentioned makes it return a value.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    would you mind sharing where you put it and how you did it. I have tried it and I still get datatype error

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Sum(Nz([SumOfRental Order TCV],0))+Sum(Nz([SumOfRental Credit Order Revenue],0))
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    Thank you so much. I had just done the same thing and it is now working as expected. I really appreciate the help. Was driving me nuts.

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. How to join on fields containing nulls
    By Atoga in forum SQL Server
    Replies: 3
    Last Post: 11-27-2015, 08:55 AM
  2. Summing Multiple Queries & Summing Time
    By WEJ in forum Queries
    Replies: 2
    Last Post: 10-04-2013, 04:46 PM
  3. Nulls problem, hmm
    By redbull in forum Programming
    Replies: 6
    Last Post: 06-29-2012, 12:16 PM
  4. SUM in regards to nulls
    By detlion1643 in forum Access
    Replies: 5
    Last Post: 02-03-2010, 08:50 AM
  5. GetRows - Invalid Use of Nulls
    By Wannabe_Pro in forum Programming
    Replies: 3
    Last Post: 07-22-2009, 07:07 AM

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