Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154

    Lightbulb Unanswered: Change visability of field in report, depending on value.

    Greets;

    I posted a similar msg, but however i didn't get a useable response, so am trying to re-word my question.

    What i have is one record (a count or avg) in one field. the value of this record is variable and could be anwhere between (in the case of the avg) 1 and 5.

    In my report i have a neat scale that goes from.

    [Good] > [Okay] > [Poor]

    I would like to display my average below one of these headings if the value is either 1-2(good) 2-4(ok) 4-5(poor).

    At the moment, i have 3 queries to group the values into these headings, but i was wondering if it was possible to have the report decide how to place the record???

    Eg; if the value is 2.4 it displays under [Okay], 1.1 under [good] and so on....

    Someone mentioned usinf IIf in reports, but I'm not sure how the expression would be writen in a text field.

    Any help would be of great value.,

    Dan
    sometimes simple is best.... and i'm just a simple fellow.

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    Need to write some code

    in the format part of the report

    on the report you have text boxes
    1 .. txtgood
    2 .. txtokay
    3 .. txtpoor


    Would would write something like


    Select Case [Value]
    Case 1 to 2
    me.txtgood.visible= True
    me.txtokay.visible= False
    me.txtPoor.visible= False
    Case 2 to 4
    me.txtgood.visible= False
    me.txtokay.visible= True
    me.txtPoor.visible= False
    Case 4 to 5
    me.txtgood.visible = False
    me.txtokay.visible = False
    me.txtPoor.visible = True
    Case else 'just a error check
    me.txtgood.visible = true
    me.txtokay.visible = true
    me.txtPoor.visible = true


    End select
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    That looks useful,

    will try tomorow, and let ya know.

    Thanks
    sometimes simple is best.... and i'm just a simple fellow.

  4. #4
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154

    Help me pleaaaaseeee!!!!!!!!

    Greets, thanks for your help but it didn't qork for some reason and displayed nothing.

    I'll try and re=word my question in the hops that someone may know an answer for this.....

    I have a value, called [Average] and can be a number between 1 and 5 based on scores in a questionairre. (The value of [Average] can change as more records are added to the database).

    What i have is a chart on my report and it has three boxes, representing thos who felt that it was good (score of 1) or bad, (score of 5) or okay (score 3). In a query these are averaged to give an overall average of what people scored. The boxes on my charty basically go from Green to Red (Good to bad) and I would like to display the result at an apporpriate place on the chart depending on what the value is in my DB.

    So if the value for Average is say 3.1, the text is displayed centrally on the chart (on the amber section) if it is 4.5 (on the green section) and 1.1 on the red section.

    So far i am able to do this with 3 queries (with the criteria set for each grouping level (Bad/Ok/Good) this the involves 3 sub reports, placing the value in the location i want it, then an additional report has all 3 subreports placed one on top of the other and this is working.

    The only hitch i have is that i have 30 questions which equates to about 120 seperate queries, and thus 120 seperate sub reports. Which is a little too much.....

    surely there is a way i am able to create the same more or less effect without any of the hassle, in just one report per question???

    Thanks again all for your time.

    Dan
    sometimes simple is best.... and i'm just a simple fellow.

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    not sure i have 100% understood your question.

    see if the attached gives inspiration (to a solution or to a revised question). have a look at both reports...

    izy
    Attached Files Attached Files
    currently using SS 2008R2

  6. #6
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    Thanks!

    That is exactly what i wanted to do before, however, it didn't work for me when i used that code in my report.

    I shall try again and see if i can do soemthing with it.

    thanks;

    DAn
    sometimes simple is best.... and i'm just a simple fellow.

  7. #7
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    Need a last bit of help,

    there is one line ( Case = 3 )

    However i want this to be

    Case >=3 and < 4

    But i get an error when i type this "Expected Expression"

    Is there another way to get the result (to show values between 3 and 4)?

    thanks

    Dan
    sometimes simple is best.... and i'm just a simple fellow.

  8. #8
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    Never mind, removed the AND and it wors fine now...

    Sorry;

    Dan
    sometimes simple is best.... and i'm just a simple fellow.

Posting Permissions

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