Follow up on the Text "ccon" field Logic
This question is a continuation of a related question.
tblMain.ccon is a text field of "Yes" and "No" TEXT. It's not a checkbox, it's in fact a Text field.
How can I "swap or re-create" this line .... "iif ( (sum( iif( nz([CCon],'No') = 'Yes' , 1, 0))) > 0 , 1, 0) + sum ( iif( nz([CCon],'No') = 'Yes' , 0, 1))..."
...... into the Query in code snippet such that it changes the query from a "checkbox" type to a "Text" type "Yes and No" TEXT
such that the query will still output same result as if it's a checkbox. The query in code snippet is from one of my old EE posts
"GetGroupIDNameCourseType" is a Public Function in my db. If the above "Yes and No" logic is properly swapped or defined, the function and my query should work just fine.
However if a sample db is still required here for visualization, please request it and I will upload it.
SELECT SUBQ.GroupID, SUBQ.CourseGroup, SUBQ.SType, sum( SUBQ.CountOfRID ) AS CountOfRID, "V004C" AS query_versionFROM (SELECT GetGroupIDNameCourseType(tblcourse.Course,tblmain.stypeid) AS GroupID,[Course] AS CourseGroup, tblSType.SType, iif( NZ([tblMain].[CCon],False)=True,-1,tblmain.rid) as ccongroup, sum( iif( NZ([tblMain].[CCon],False)=True,0,1)) + max( iif( NZ([tblMain].[CCon],False)=True,1,0)) AS CountOfRID, tblmain.groupid as individual_groupidFROM ((tblMain INNER JOIN tblCourse ON tblMain.CourseID = tblCourse.CourseID) inner join tblSType on tblSType.STypeID = tblmain.STypeid)WHERE tblMain.Appdate >=[forms]![frmReportDateRange]![BeginDate] And tblMain.Appdate<=[forms]![frmReportDateRange]![EndDate]GROUP BY GetGroupIDNameCourseType(tblcourse.Course,tblmain.stypeid),[Course], tblSType.SType, iif( NZ([tblMain].[CCon],False)=True,-1,tblmain.rid), tblmain.groupid) AS SUBQGROUP BY SUBQ.GroupID, SUBQ.CourseGroup, SUBQ.stype;
iif( nz([CCon],'No') = 'Yes' , 0, 1)
But I prefer to write function:
Function ccon_bool(CC as string) as boolean
ccon_bool = false
if isnull(CC) = false and ucase(CC) = "YES" then ccon_bool = true
and use this function in query: