Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Apr 2001
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access questions (Access 2000)

    I have a user that wants to do a couple things. I thought I would throw these questions up on the board for some quick help........The first is that she would like to get an average of a field, but does not want to include the values that are zero. The second is that she would like the text to change on a report if a field is = to an extablished value. I have not done any checking on this as of yet. Just enough help to get me started is all I am looking for........Thanks!!!

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access questions (Access 2000)

    For the average, add the field a second time in the query, in the total line enter Where and in the criteria enter <>0.
    For the report:
    In the On format event of the detail section of the report enter something like this :
    <pre>If Me.MyTextBox = "Something" Then
    Me.MyOtherTextBox = "AAAA"
    Else
    Me.MyOtherTextBox = "BBBB"
    End If</pre>

    Francois

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

    Re: Access questions (Access 2000)

    First question:

    If the user computes the average in a totals query, she can add the relevant field twice.
    In one instance, set the Totals option to Avg. In the other, set it to Where (setting it to Where automatically deselects the Display checkbox), and enter <>0 in the first Criteria row. It is important NOT to enter the criteria in the column with the average, because in that case the criteria would not be applied to the individual values, but to the average.

    An alternative is the DAvg function:

    DAvg("[FieldName]", "[TableName]", "[FieldName]<>0")

    Second question:

    On the report, she can put an unbound text box with control source

    =IIf([FieldName]=value,"Text to be displayed for specific value","Text to be displayed otherwise")

    Either of the texts to be displayed can be empty ("").
    If the field is a text field, the value must be enclosed in quotes.

  4. #4
    2 Star Lounger
    Join Date
    May 2002
    Location
    Dubai, UAE, United Arab Emirates
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access questions (Access 2000)

    G'day

    As an alternative to IIF, if you have more than one test to perform, I find Switch does a reasonbale job as long as your requirements aren't too numerous and aren't likely to change :

    = Switch([field1] = "aaa","aAa",[field1]="bbb","bBb",field1="ccc" ... etc.)

    Although ... Is Switch available in A2K ?

    Regards Ken

    P.S. with regards to the Averaging except for Zero - are any of your values Null or Empty ? If so you'd better use a NZE function instead of "[FieldName]<>0 ie:

    Davg("[FieldName]","TableName","Not NZE([FieldName])")

    NZE :
    Public Function NZE(DataIn As Variant) As Boolean
    If IsNull(DataIn) Then
    ....NZE = True
    ElseIf IsEmpty(DataIn) Then
    ....NZE = True
    ElseIf DataIn = 0 Then
    ....NZE = True
    ElseIf DataIn = "" Then
    ....NZE = True
    Else
    ....NZE = False
    End If
    End Function

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

    Re: Access questions (Access 2000)

    Null values aren't included in aggregation functions, so you don't have to account for those.
    I don't think that a numeric field can be "Empty"; IsEmpty applies to VB variables.
    And numeric fields can't be "" either.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access questions (Access 2000)

    Switch is still available. It's a VBA function rather than an Access function anyhow.
    Charlotte

  7. #7
    New Lounger
    Join Date
    Apr 2001
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access questions (Access 2000)

    I have been tied up so I am just getting back to this. For an example how would you code that you want it to be red if the value is greater than 100?

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

    Re: Access questions (Access 2000)

    You can apply conditional formatting to text boxes in Access 2000 (Format/Conditional Formatting menu option). Since I still work with Access 97, I can't tell you if this will work in reports. Do a bit of experimenting, or ask.

    It's also possible to use code. In a variation on Francois' example:

    If Me.MyTextBox > 100 Then
    Me.MyOtherTextBox.ForeColor = vbRed
    Else
    Me.MyOtherTextBox.ForeColor = vbBlack
    End If

    The ForeColor property is the text color.

    You can also use the BackColor property; that is the fill color. This only works if the BackStyle property of the text box is set to Normal.

Posting Permissions

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