Results 1 to 3 of 3
  1. #1
    Jess_Wundring is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2010
    Posts
    2

    Question Suppressing #Name? Error

    Hi All,

    I created a pretty complex transform/pivot query that summarizes the number of type X in category Y over a given time period. I use CreateQueryDef to temporarily store the sql statement in the database. The results get displayed in a datasheet view or printed as a query.

    The problem I'm having is, if the time period is too short, so that not all X (or perhaps Y) have values, one or more cells contain the #Name? error. I need to find a way to suppress that error, so that those cells are blank or zero or....



    Thanks!
    Jess

  2. #2
    Datagopherdan is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Dec 2008
    Posts
    220
    Try using NZ to compensate for any fields that do not have values, e.g. MyValue:Nz([YourFieldName])

    Dan
    Access Development

  3. #3
    Jess_Wundring is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2010
    Posts
    2

    Red face

    Try using NZ to compensate for any fields that do not have values, e.g. MyValue:Nz([YourFieldName])
    I don't use Access as often as I'd like, so I'm not sure how I might implement this, but here's the pertinent code snippet:

    Code:
     
    strParams = fnConstructWhere(strParams)
     
    strSQL = " TRANSFORM Count(tblcases.caseid) AS Cases " _
    & " SELECT tblcases.casetype, Count(tblcases.caseid) AS [Total Cases]" _
    & " FROM tblcases"
     
    strSQL = strSQL & strParams & " GROUP BY tblcases.casetype PIVOT tblcases.status;"
     
    Set MyQuery = MyDB.CreateQueryDef(strQName, strSQL)
    DoCmd.OpenQuery strQName, acViewPreview, acReadOnly
    Please Help?

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

Similar Threads

  1. Replies: 3
    Last Post: 10-15-2009, 12:24 PM

Tags for this Thread

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