Null value issue

[brian williams]

I have a problem where the Approved value (SQL) has a 1,0 or NULL value. I can not figure out how to show the null value when returned for the filtered input.

<cfset vApproved = 0>
<cfif isDefined("form.cboApproved")>
    <cfif form.cboApproved eq "">
        <cfset vApproved = 0>
    <cfelse>
        <cfset vApproved = #form.cboApproved#>
    </cfif>
<cfelse>
    <cfset vApproved = 0>
</cfif>
 

<td>
<select name="cboApproved" class="smallFont">
<!---<option value="All">All</option>--->
 <option value="1"
   <cfif isDefined("vApproved")>
    <cfif vApproved eq 1>
    selected
    </cfif>
   </cfif>
>True</option>
  <option value="0"
    <cfif isDefined("vApproved")>
         <cfif vApproved eq 0>
     selected
     </cfif>
   </cfif>
>False</option>
  <option value= "">Need Approval</option>
    </select>
</td>
 

Query pulling the information below.

<cfquery name="qRecords" datasource="VarRenum_Spaisdata3">
    SELECT
 
        [VR_Machine_Name] as ActivityType
        ,[Activity]
 
        ,[VR_Department_Number]
        ,[FullName]
 
        ,[Approved]
        ,[ApprovedBy]
        ,[ApprovedDate]
 
        ,[Login_Status]
        ,[Login_Date]
        ,[TotalTime]
 
        ,[VR_Machine_Participant_Activity_Login_ID]
        ,[VR_Machine_Participant_Activity_ID]
        ,[VR_Activity_ID]
        ,[VR_Participant_ID]
        ,[VR_Machine_Participant_ID]
        ,[VR_Machine_ID]
 
        ,[TeamMember_Activity]
        ,[Last_LogInOrOut]
        ,[Logout_Date]
        ,[Inactive]
        ,[InactiveDate]
        ,[InactivatedBy]
        ,[RecCreatedBy]
        ,[RecCreatedDate]
        ,[RecUpdatedBy]
        ,[RecUpdatedDate]
        ,[Method]
        ,[Manning_Factor]
        ,[Approval_Required]
        ,[Batch_Approval_Allowed]
        ,[Batch_LogoutByBUL]
        ,Department
        ,BusinessUnit
        ,Crew
        ,Value_Nvarchar
    FROM [VariableRemuneration].[dbo].[vw_web_Machine_Participant_Activity_Logins] ma
    WHERE
        ma.Approved = '#variables.vApproved#' and
        ISNULL(ma.Approval_Required,0) = '#variables.vApprovalRequired#'
        and ma.BusinessUnit like '#variables.vBusUnit#'
        <!---AND Login_Date Between '#vBeginDate#' AND '#vEndDate#'--->
        <!---AND Login_Status = '#vLoginStatus#'--->
        <!---AND ma.Login_Status = 'In'--->
    ORDER BY ma.Login_Date
</cfquery>
 

[brian williams]

I need some help getting syntax on an issue. I have a listbox with 3 values, True(1), False(0) and Needs Approval(NULL) but I can't seem to get the NULL to show. The SQL column has this as a bit value so a Case adjustment does not work.

What I need this to do is if the Needs Approval value is selected in the cboApproved box then use the 'qRecordsNull" query to get the results, else use the "qRecords" query.

Below is the code that I have that works for true and false. Any help would be greatly appreciated.

<cfset vApproved = 0>
<cfif isDefined("form.cboApproved")>
    <cfif form.cboApproved eq "">
        <cfset vApproved = 0>
    <cfelse>
        <cfset vApproved = #form.cboApproved#>
    </cfif>
<cfelse>
    <cfset vApproved = 0>
</cfif> 

<td>
                            <select name="cboApproved" class="smallFont">
                                <!---<option value="All">All</option>--->
                                   <option value="1"
                                    <cfif isDefined("vApproved")>
                                        <cfif vApproved eq 1>
                                          selected
                                        </cfif>
                                    </cfif>
                                >True</option>
                                   <option value="0"
                                    <cfif isDefined("vApproved")>
                                        <cfif vApproved eq 0>
                                          selected
                                        </cfif>
                                    </cfif>
                                >False</option>
                                   <option value= "">Need Approval</option>
                            </select>
                        </td> 

[acoder]

You could have it as -1 and then check for that within the output of the select element to show the selected option.