Error 13 when checking Boolean statement

How about

If CBool("Me.[reportSubForm].Form.Controls(" & ctl.Name & ").ColumnHidden") = False Then

Select allOpen in new window

Same  result.

If you put a breakpoint on this line what do you get ? if its Null it won't CBool...you need Nz first but...put the breakpoint and let us know...

Boolean (true/false) data; Access stores the numeric value zero (0) for false, and -1 for true.
Make your If statement "If CBool("Me.[reportSubForm].Form.[" & ctl.Name & "].ColumnHidden") = 0 Then"

At the breakpoint this is what I get

?"Me.[reportSubForm].Form.Controls(" & ctl.Name & ").ColumnHidden"
Me.[reportSubForm].Form.Controls(LastName).ColumnHidden

Paul, I tried "If CBool("Me.[reportSubForm].Form.[" & ctl.Name & "].ColumnHidden") = 0 Then" with the  same error 13 happening

I would say this:

Me.[reportSubForm].Form.Controls(LastName).ColumnHidden

need to become

Me.[reportSubForm].Form.Controls("LastName").ColumnHidden

So this

 If CBool("Me.[reportSubForm].Form.[" & Chr(34) &  ctl.Name & Chr(34) & "].ColumnHidden") = False Then

Joe, This was one of my ideas also, but did a copy/paste with your string to make sure I didn't typo, and still the same result.

This is what the strings is in the Immediate window
?"Me.[reportSubForm].Form.[" & Chr(34) & ctl.Name & Chr(34) & "].ColumnHidden"
Me.[reportSubForm].Form.["LastName"].ColumnHidden

By same result ... you mean you got an error ?
Try this

"Me.(reportSubForm).Form.Controls(" & Chr(34) & ctl.Name & Chr(34) & ").ColumnHidden"

Mainly parens instead of brackets.

Pat, you are a life (and hair) saver!!!!!

Thanks a million!

I saved your hair but who will save mine?????

Oh, sorry... I missed that you were trying to concatenate the object name.  You'd think that would work, but it doesn't.  (Maybe John can explain why;  I don't know.)

This works for me;  I commented out two lines in your code and replaced them to work with my objects (I'm not using the ColumnHidden property anywhere), and got a list of all the visible controls:

   Dim ctl As Control, varProperty As Variant
   
'   For Each ctl In Me.reportSubForm.Controls
    For Each ctl In Me.Controls
   
        If ctl.Name <> "_Label" Then
            varProperty = ctl.Visible
'           If CBool("Me.[reportSubForm].Form.[" & ctl.Name & "].ColumnHidden") = False Then
            If varProperty = "True" Then
             Debug.Print ctl.Name
             End If
        End If
Next

Select allOpen in new window

Paul,

This function was tied to a subform that the user manipulates with filters and hiding columns of data to export to an excel file (which is still an open question of mine on this forum, but now that this works, I think I have my answer for the other question and will post my code when I get it done). The visible and HiddenColumn  are two different properties and don't show the same results in my situation. If I would hide a column on this subform, the .Visible property was still set to true.

Thanks for all the help everyone!

Oooooh, that's clever!  Definitely post the code.  :)

Paul,
Here is what I adapted to work with my quick database. The query is defined in the form by way of a listbox with several query names that populate a subform with a datasheet style data.

The problem I had was parsing the made up or calculated field names not part of the underlying table field and those columns still show in the excel file. But in the end, it did what I need to do.

Someone with more experience could make this more universal and adapting to the SQL string which I would love to see.

If someone wants to see a database, I can make a striped down version.

Dim strSQL As String
    Dim strSQLSelect As String
    Dim strSQLFrom As String
    Dim strSQLGroup As String
    Dim strSQLHaving As String
    Dim strSQLOrder As String
    Dim strFile As String
    Dim strQry As String
    Dim db As dao.Database
    Dim Qdf As dao.QueryDef
    Dim ctl As Control
    Dim rs As dao.Recordset
    Dim i, j, k, l As Integer
    strSQL = GetQuerySQL(Me.[reportSubForm].Form.RecordSource) 'Get the current SQL String and parse it
    i = InStr(1, strSQL, "FROM")
    j = InStr(1, strSQL, "GROUP")
    k = InStr(1, strSQL, "HAVING")
    l = InStr(1, strSQL, "ORDER BY")
    strSQLSelect = Mid(strSQL, 1, i - 1)  'SELECT part
    For Each ctl In Me.reportSubForm.Controls
        If CStr(Right(ctl.Name, 6)) <> "_Label" Then
            If (ctl.ColumnHidden) <> False Then
                If InStr(1, strSQLSelect, "[") > 0 Then
                    If Mid(strSQLSelect, InStr(1, strSQLSelect, ctl.Name) - 1, 1) = "[" Then
                        strSQLSelect = Replace(strSQLSelect, "FamilyTbl.[" & ctl.Name & "],", "")
                    Else
                        strSQLSelect = Replace(strSQLSelect, "FamilyTbl." & ctl.Name & ",", "")
                    End If
                Else
                    strSQLSelect = Replace(strSQLSelect, "FamilyTbl." & ctl.Name & ",", "")
                End If
            End If
        End If
    Next
    strSQLSelect = TrimMultiSpaces(strSQLSelect)
    If i > 0 Then strSQLSelect = strSQLSelect & Mid(strSQL, i, (j - i) - 1)   'FROM
    If j > 0 Then strSQLSelect = strSQLSelect & Mid(strSQL, j - 1, (k - j) - 1)  'GROUP
    If k > 0 Then strSQLSelect = strSQLSelect & Mid(strSQL, k - 1, (l - k) - 1)  'HAVING
    If l > 0 Then strSQLSelect = strSQLSelect & Mid(strSQL, l - 1, Len(strSQL))  'ORDER BY
'start the excel process
    strFile = SpecialFolderPath("desktop") & "\Export.xls"
'If the temp target file exists, delete it first
    If FileExists(strFile) Then
        KillProperly strFile
    End If
    strQry = "qryTempFile"
    Set db = CurrentDb
    On Error Resume Next
    With db
        .QueryDefs.Delete (strQry)
        Set Qdf = .CreateQueryDef(strQry, strSQLSelect)
    End With
    
    Set rs = Qdf.OpenRecordset
    Dim xlApp As Object
    Set xlApp = CreateObject("Excel.Application")
    With xlApp
        .Visible = True
        .Workbooks.Add
        .Sheets("sheet1").Select
        .ActiveSheet.Range("A2").CopyFromRecordset rs
        For i = 1 To rs.Fields.Count
            xlApp.ActiveSheet.Cells(1, i).value = rs.Fields(i - 1).Name
            Next i
            xlApp.Cells.EntireColumn.AutoFit
        End With

Select allOpen in new window

And the Functions, some I made, some I borrowed

Public Function GetQuerySQL(MyQueryName As String) As String
Dim QD As dao.QueryDef
 
Set QD = CurrentDb.QueryDefs(MyQueryName)
GetQuerySQL = QD.SQL
 
End Function

Select allOpen in new window

Public Function TrimMultiSpaces(strSample As String)
    Do While InStr(1, strSample, "  ")
        strSample = Replace(strSample, "  ", " ")
    Loop

    TrimMultiSpaces = Trim(strSample)
End Function

Select allOpen in new window

 ' Options For specical folders
 '    AllUsersDesktop
 '    AllUsersStartMenu
 '    AllUsersPrograms
 '    AllUsersStartup
 '    Desktop
 '    Favorites
 '    Fonts
 '    MyDocuments
 '    NetHood
 '    PrintHood
 '    Programs
 '    Recent
 '    SendTo
 '    StartMenu
 '    Startup
 '    Templates
Function SpecialFolderPath(strFolder As String) As String
     
    Dim objWSHShell As Object
    Dim strSpecialFolderPath
     
     'On Error GoTo ErrorHandler
     ' Create a shell object
    Set objWSHShell = CreateObject("WScript.Shell")
     ' Find out the path to the passed special folder,
     '  just change the "Desktop" for one of the other options
     SpecialFolderPath = objWSHShell.SpecialFolders.Item(CVar(strFolder))
    'SpecialFolderPath = objWSHShell.SpecialFolders(strFolder)
     ' Clean up
    Set objWSHShell = Nothing
    Exit Function
ErrorHandler:
     
    MsgBox "Error finding " & strSpecialFolder, vbCritical + vbOKOnly, "Error"
End Function

Select allOpen in new window

Function FileExists(ByVal FileToTest As String) As Boolean
   FileExists = FileToTest <> ""
End Function

Select allOpen in new window

Public Function KillProperly(Killfile As String)
    If Len(Dir(Killfile)) > 0 Then
        SetAttr Killfile, vbNormal
        Kill Killfile
    End If
End Function

Select allOpen in new window