Results 1 to 2 of 2
  1. #1
    juliak is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    1

    Exclamation TRANSFORM Nz ERROR, Help!

    Hi everyone! I am new to Access and just starting to learn the SQL language. I am trying to run a query with a TRANSFORM Nz function so that if a query I run yields a null result it will produce "0" instead (I am using a count in the query). I have read up as much as I can on this but still cannot get my query to function. I've seen people yield a 0 for a null count in many different types of ways, none of which worked for me. I will post my code to see if anyone could help me. I would appreciate any insight you may have and any advice you can give me. Thank you!

    TRANSFORM Nz(Count(AuditStatus.[UniqueAuditID*]),0)
    SELECT Count(AuditStatus.[UniqueAuditID*]) AS [CountOfUniqueAuditID*], StatusType.StatusType, [AuditTypes(GCP,PV,GLP)].AuditType
    FROM StatusType INNER JOIN ([AuditTypes(GCP,PV,GLP)] INNER JOIN (AuditStatus INNER JOIN AuditByType ON AuditStatus.[UniqueAuditID*] = AuditByType.AuditS_ID) ON [AuditTypes(GCP,PV,GLP)].ID = AuditByType.AuditType_ID) ON StatusType.ID = AuditStatus.Status_ID


    GROUP BY StatusType.StatusType, [AuditTypes(GCP,PV,GLP)].AuditType
    HAVING (((StatusType.StatusType)="postponed") AND (([AuditTypes(GCP,PV,GLP)].AuditType)="GLP"));

    Not sure if this is relevant but the asterisk in UniqueAuditID* field is apart of the name.

  2. #2
    Join Date
    Apr 2017
    Posts
    1,668
    Hi

    I'm not sure this is the cause here, bur don't use any special characters like *, -, /, etc in field or file names - otherwise you never know when you have some problem.

    Try:
    TRANSFORM Count(Nz(AuditStatus.[UniqueAuditIDX],0))
    SELECT Count(Nz(AuditStatus.[UniqueAuditIDX],0)) AS [CountOfUniqueAuditIDX], StatusType.StatusType, [AuditTypes(GCP,PV,GLP)].AuditType
    FROM StatusType INNER JOIN ([AuditTypes(GCP,PV,GLP)] INNER JOIN (AuditStatus INNER JOIN AuditByType ON AuditStatus.[UniqueAuditIDX] = AuditByType.AuditS_ID) ON [AuditTypes(GCP,PV,GLP)].ID = AuditByType.AuditType_ID) ON StatusType.ID = AuditStatus.Status_ID
    GROUP BY StatusType.StatusType, [AuditTypes(GCP,PV,GLP)].AuditType
    HAVING (((StatusType.StatusType)="postponed") AND (([AuditTypes(GCP,PV,GLP)].AuditType)="GLP"));

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

Similar Threads

  1. Best way to transform a table
    By siema24 in forum Access
    Replies: 5
    Last Post: 01-27-2017, 03:01 AM
  2. Transform and resend email
    By Sargedessy in forum Programming
    Replies: 3
    Last Post: 08-18-2016, 09:11 AM
  3. Transform Data .. pls help!
    By Toble in forum Queries
    Replies: 14
    Last Post: 02-23-2015, 05:34 PM
  4. Transform data model from Access to X
    By snoopy in forum Database Design
    Replies: 2
    Last Post: 05-29-2012, 12:37 AM
  5. PIVOT TRANSFORM in MSAccess
    By Vin in forum Queries
    Replies: 1
    Last Post: 05-19-2011, 09:03 AM

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