Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Another Conditional Format (2k)

    I read the posts here relating to conditional formatting of a control in a report, without finding a way (or understanding how to) format a control on a report based on the value in another control. I am needing to change the format of a control from/to PerCent with two decimal places and Currency with no decimal. I was struggling with something like the following but am not familiar enough with the properties of individual controls on a report.

    If Me.txtProfile = "Num" Then
    Me.txtAverageClosed.Properties.Format = Percent
    Me.txtAverageClosed.Properties.Decimal = 2
    Else
    Me.txtAverageClosed.Properties.Format = Currency
    Me.txtAverageClosed.Properties.Decimal = 0
    End If

    I know this is not the correct syntax, but included as a part of explaining my challenge.

    Thanks in advance for your ideas.

    Ken

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Another Conditional Format (2k)

    Conditional formatting can set the following properties of a text box:
    - Background (fill) color
    - Foreground (font) color
    - Bold, Italic, Underline
    - Enabled/disabled
    You cannot specify the number format in conditional formatting.

    You could use code in the On Format event of the section that contains the text boxes (the detail section or a group header or footer). To find out which properties you need in VBA, click in the corresponding property in design view. The help subject will tell you the name of the property in VBA, what type it is and provide some examples. Format is a string, so you must specify a value between quotes.

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If Me.txtProfile = "Num" Then
    Me.txtAverageClosed.Format = "Percent"
    Me.txtAverageClosed.DecimalPlaces = 2
    Else
    Me.txtAverageClosed.Format = "Currency"
    Me.txtAverageClosed.DecimalPlaces = 0
    End If
    End Sub

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Another Conditional Format (2k)

    Hans,

    That was exactly what I needed. I just didn't know the syntax. I have used the autocomplete in VB as a crutch, and didn't know I could get help in the design view using the method you stated.

    THANKS!

    Ken

  4. #4
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Another Conditional Format (2k)

    Hans,

    I noticed in the Access Help samples they use "Me!" instead of "Me.".. What is the reason for the difference? Is there a resource I should read to know more?

    Thanks again!

    Ken

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Another Conditional Format (2k)

    You can use both. The dot / period / point is used to indicate a property (or method) of an object, and the bang / exclamation mark to indicate a member of a collection.
    A control on a form or report is both a property of the form / report and a member of the Controls collection, which is its default property.

    So Me.txtSomething and Me!txtSomething are both valid, as are Me.Controls!txtSomething and Me.Controls("txtSomething") and Me("txtSomething").

  6. #6
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Another Conditional Format (2k)

    Nothing beats simplicity. LOL For me, not being familiar with the syntax in VB, I have found using the period/dot convenient as it gives me options from which to choose. It is good to know that there are alternatives though.

    THANKS again for your help!

    Ken

Posting Permissions

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