Displaying query field as checkbox with iff statement 2

[Moss100]

Hello I have the following field in my query:

S/c_Same: IIf([Payment_Bank_Sort_Code]=[Sort Code],True,False)

It works fine and returns -1 or 0 as expected.

I would like the query to display the result using a checkbox, but the Lookup, Display Control does not offer this option.

What do I need to do to get it to display as a checkbox.

Many thanks Mark

 

[dhookom]

Why are you not displaying the records in a form where you can use a checkbox?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MajP]

Concur. If you are worried about formatting you should be using a form.
You may be able to change the query display control by using the fields collection of the querydef. However then you will likely have to figure out the property of the properties collection of the field and modify that.

 

[Moss100]

Thanks for the input - I am using some queries just for back end data cleansing - I don't really want to create forms.

I have a kind of work round using Wingdings to show either a ticked or unticked box, which suffices.

Many thanks Mark

 

[MajP]

It can be done as I thought. Here is the code to turn a calculated field to display as checkbox.

Code I wrote

Public Sub CreateCheckBox()
  Dim db As dao.Database
  Dim qdf As QueryDef
  Dim fld As dao.Field
  Dim prp As dao.Property
  Set db = CurrentDb
  Set qdf = db.QueryDefs("qry_Test")
  Set fld = qdf.Fields("calc")
  SetPropertyDAO fld, "DisplayControl", dbInteger, CInt(acCheckBox)
End Sub

Allen Brownes code from internet

'Constants for examining how a field is indexed.
Private Const intcIndexNone As Integer = 0
Private Const intcIndexGeneral As Integer = 1
Private Const intcIndexUnique As Integer = 3
Private Const intcIndexPrimary As Integer = 7

Function SetPropertyDAO(obj As Object, strPropertyName As String, intType As Integer, _
    varValue As Variant, Optional strErrMsg As String) As Boolean
On Error GoTo ErrHandler
    'Purpose:   Set a property for an object, creating if necessary.
    'Arguments: obj = the object whose property should be set.
    '           strPropertyName = the name of the property to set.
    '           intType = the type of property (needed for creating)
    '           varValue = the value to set this property to.
    '           strErrMsg = string to append any error message to.
    
    If HasProperty(obj, strPropertyName) Then
        obj.Properties(strPropertyName) = varValue
    Else
        obj.Properties.Append obj.CreateProperty(strPropertyName, intType, varValue)
    End If
    SetPropertyDAO = True

ExitHandler:
    Exit Function

ErrHandler:
    strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & " not set to " & varValue & _
        ". Error " & Err.Number & " - " & Err.Description & vbCrLf
    Resume ExitHandler
End Function

Public Function HasProperty(obj As Object, strPropName As String) As Boolean
    'Purpose:   Return true if the object has the property.
    Dim varDummy As Variant
    
    On Error Resume Next
    varDummy = obj.Properties(strPropName)
    HasProperty = (Err.Number = 0)
End Function

 

[Moss100]

Thanks All