Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Displaying query field as checkbox with iff statement

Displaying query field as checkbox with iff statement

Displaying query field as checkbox with iff statement

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

RE: Displaying query field as checkbox with iff statement

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

Hook'D on Access
MS Access MVP 2001-2016

RE: Displaying query field as checkbox with iff statement

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.

RE: Displaying query field as checkbox with iff statement

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

RE: Displaying query field as checkbox with iff statement

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
        obj.Properties.Append obj.CreateProperty(strPropertyName, intType, varValue)
    End If
    SetPropertyDAO = True

    Exit Function

    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 

RE: Displaying query field as checkbox with iff statement

Thanks All :)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close