locked
How do I use NZ function and display the column name? RRS feed

  • Question

  • Hi Folks -

    How do I use the NZ functional and display the actual column name instead of expr1?

    This doesn't work due to the circular reference error:

    SELECT
    				NZ(ActEmpFTE,"NULL") AS [ActEmpFTE],
    	NZ(ActEmpId,"NULL") AS [ActEmpId],
    	NZ(ActEmpLoc,"NULL") AS [ActEmpLoc],
    	NZ(ActEmpMgr,"NULL") AS [ActEmpMgr],
    	NZ(ActEmpName,"NULL") AS [ActEmpName],
    	NZ(ActEmpPos,"NULL") AS [ActEmpPos],
    	NZ(ActEmpTaskCode,"NULL") AS [ActEmpTaskCode],
    	NZ(ActEMpTaskName,"NULL") AS [ActEMpTaskName],
    	NZ(ActivityCode,"NULL") AS [ActivityCode],
    	NZ(ActPercentTimevalue,"NULL") AS [ActPercentTimevalue],
    	NZ(ActHeaderCustom1,"NULL") AS [ActHeaderCustom1],
    	NZ(CompanyCode,"NULL") AS [CompanyCode],
    	NZ(DepartmentName,"NULL") AS [DepartmentName],
    	NZ(PersonnelSubArea,"NULL") AS [PersonnelSubArea],
    	NZ(FunctionalArea,"NULL") AS [FunctionalArea],
    	NZ(CostCenter,"NULL") AS [CostCenter],
    	NZ(ActPrjCode,"NULL") AS [ActPrjCode],
    	NZ(ActPrjName,"NULL") AS [ActPrjName],
    	NZ(ActPrjType,"NULL") AS [ActPrjType],
    	NZ(ActProgram,"NULL") AS [ActProgram],
    	NZ(ActProgramInitiative,"NULL") AS [ActProgramInitiative],
    	NZ(ActTaskCode,"NULL") AS [ActTaskCode],
    	NZ(ActTimevalue,"NULL") AS [ActTimevalue],
    	NZ(ActWeekBeginDate,"NULL") AS [ActWeekBeginDate],
    	NZ(ActDescription,"NULL") AS [ActDescription],
    	NZ(PrjSystems,"NULL") AS [PrjSystems]
    FROM [dbo_V_TEMPO_MnthlyExport]
    WHERE Format(ActWeekBeginDate,"yyyy") = 2019 AND Format(ActWeekBeginDate,"m")= 1
    ORDER BY ActWeekBeginDate ASC;

    How do I get around it?

    Thursday, July 9, 2020 9:54 AM

All replies

  • You can't.  You have to give it another name.

    I guess one way would be to use your current query as a subquery and in the main query name the fields back to the original field names.

    Another option would be to not use the NZ() within your query, but rather within the report controls.


    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Thursday, July 9, 2020 10:12 AM
  • You can do it by prefixing the field name in the Nz function with the table name: change

    NZ(ActEmpFTE,"NULL") AS [ActEmpFTE]

    to

    NZ([dbo_V_TEMPO_MnthlyExport].ActEmpFTE,"NULL") AS [ActEmpFTE]

    and similar for all the other fields.


    Regards, Hans Vogelaar (https://www.eileenslounge.com)


    Thursday, July 9, 2020 10:38 AM
  • Thank you for sharing that Hans, I never knew that trick.

    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Thursday, July 9, 2020 11:45 AM
  • This is great!!!! Thank you both for your guidance!!
    Thursday, July 9, 2020 1:43 PM