Results 1 to 6 of 6

Thread: check box query

  1. #1
    Join Date
    Nov 2005

    Unanswered: check box query

    can anybody help....

    I am trying to produce a report where a checkbox has been ticked.

    is there a command I can use within the design query criteria , which selects records only if the check box has been ticked?

  2. #2
    Join Date
    Sep 2003
    WHERE is this checkbox? On a form? Is it a column of a table?
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    "Checkboxes" generally represent "yes/no" or bit fields. That's why M Owen wants to know. For querying purposes, "yes/checked" = -1, "no/unchecked" = 0.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    you could place that as a term in your reports query

    select blah from <mytable1>
    join <mytable1> on <mytable1>..<myjoincolumn> = <mytable2>.<myjoincolumn>
    where (<mybooleanValue#1>=true or <mybooleanValue#2>=true)
    order by blah
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2006
    Washington, D.C
    If you are trying to create a filter here are some functions that could help you out.

    Public sWhere As String

    Private Sub CreateReport_Click()

    stDocName = "TrackingReport"

    DoCmd.OpenReport stDocName, acViewPreview, , sWhere, acWindowNormal

    End Sub

    Private Sub Build_Query()
    Dim sClause, sFieldName, sValue As String
    Dim clauseCount As Integer
    Dim ctrl As Control
    Dim StartDate, EndDate As String
    Dim CtrName As String
    Dim MySqlClause As String

    clauseCount = 0
    For Each ctrl In Me.Controls
    If ctrl.ControlType = acCheckBox Then
    sFieldName = ctrl.StatusBarText ' this must equal a value like = 123

    If IsNull(ctrl.value) Then
    sValue = emptyStr
    sValue = ctrl.value
    End If

    CtrName = ctrl.Name

    If Not sValue = emptyStr Then
    If Not sFieldName = emptyStr Then

    If InStr(CtrName, "CheckBoxName") And ctrl.value = True Then

    sClause = sFieldName
    clauseCount = clauseCount + 1
    addClause CStr(sClause), clauseCount
    End If ' end search date

    End If 'SFieldName
    End If 'SValue
    End If ' ControlType
    Next ' end Each ctrl In Me.Controls

    End Sub

    Private Sub addClause(value As String, count As Integer)

    If count = 1 Then
    sWhere = value

    sWhere = sWhere & " OR " & value

    End If
    End Sub

    Change some stuff around and try it out...

    EDIT: I forgot to add one more function that comes in handy

    ' used to put quotes around text in SQL
    Public Function quotedStr(ByVal str As String)
    quotedStr = """" & str & """"
    End Function
    Last edited by alaoa; 01-27-06 at 09:47.

  6. #6
    Join Date
    Nov 2003
    Place the condition within the WHERE section of your DoCmd.OpenReport command. For example:

    stDocName = "Your Report Name"
    DoCmd.OpenReport stDocName, acPreview, , "[myCheckBoxNameInTable] = True"
    Self Taught In ALL Environments.....And It Shows!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts