Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    conditional formating in a report (2003)

    I would like to create a report that looks up a value in a query. If a criteria is met for one field or another then an entry is made in the report. If the criteria is not met than no entry whatsoever is made. For example if the query returns a value of 200 and the criteria is >250 the report would be blank. However if the criteria was <220 then the report would have a line e.g. " the value is positive. ('Positive' should not be the value that the query returned but some other expression e.g. positive).

    I am at a loss even where to begin. Any help would be appreciated

    Nebbia

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

    Re: conditional formating in a report (2003)

    You could create an unbound report (i.e. with a blank record source). Put a text box in the detail section with a control source like this:

    =IIf(DLookup("SomeField","SomeQuery")<200,"Your text here","")

  3. #3
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: conditional formating in a report (2003)

    Yes this will return the value from the 1st query. But i would like it to do 2 more things. Take the output of the dlookup function and cross-reference it to a matching table reference. Secondly is there a way in the report that if the IIF statement is false (blank) then it will not use a line in the report.

    nebbia

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

    Re: conditional formating in a report (2003)

    I'm not sure what ypu mean by "cross-reference it to a matching table reference." Perhaps you can use nested DLookups, something like

    =IIf(DLookup("SomeField","SomeQuery")<200,Dlookup( "OtherField","OtherTable","YetAnotherField=DLookup ("SomeField","SomeQuery")),Null)

    If you set the Can Shrink property of the text box and of the detail section to Yes, blank (null) lines will be suppressed.

  5. #5
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: conditional formating in a report (2003)

    I don't believe I have been able to express my goal. I have included a sample database which may make help me focus my questions.

    1. can the second line'2' be deleted if its is null i.e. no extra line spacing between 1 and 2. Label boxes are not necessary
    2. can the dlookup output 'highC' be used as a criteria to generate the appropriate code from table 'reference' to the text box in the report

    nebbia

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

    Re: conditional formating in a report (2003)

    1) Set the control source of the middle text box to
    <code>
    =IIf(DLookUp("result.c","qryresults")>200,"HighC", Null)
    </code>
    Set its Can Shrink property to Yes, and the Can Shrink property of the Detail section of the report as well.
    Delete the label for this text box.

    2) The text "HighC" does not occur in the table Reference, so I have no idea what you mean by this.

  7. #7
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: conditional formating in a report (2003)

    Thank you The first question seems to be resolved. I made a error in that the dlookup output should have been either c, h, or o which are present in the reference table. So if the dlookup criteria is met a 'c' is returned. In the reference table a 'c' corresponds to a code of'200' and I would like this to appear next to the dlookup box in the report

    nebbia

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

    Re: conditional formating in a report (2003)

    Say you have a text box Text3 with control source
    <code>
    =IIf(DLookUp("result.c","qryresults")<200,"C",Null )
    </code>
    This will return either "C" or a blank (null), depending on whether the condition is met. You can set the control source of another text box in the Detail section to
    <code>
    =IIf(IsNull([Text3]),Null,DLookUp("Code","Reference","Type=" & Chr(34) & [Text3] & Chr(34)))
    </code>
    The Chr(34) around the value of Text3 are double quotes <code>"</code>.

  9. #9
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: conditional formating in a report (2003)

    I do not follow the chr(34) reference. If I am to substitute a ""it is not working- it returns #name? Did I misunderstand you? Please elaborate.

    nebbia

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

    Re: conditional formating in a report (2003)

    The control source in my previous reply should be used "as is", including the Chr(34) - literally. See attached version.

  11. #11
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: conditional formating in a report (2003)

    Works perfectly. I haven't seen this reference before chr(34). Can you explain it or direct me to an explanation

    Thanks once again

    nebbia

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

    Re: conditional formating in a report (2003)

    See 9for example) <post:=230,516>post 230,516</post:>.

Posting Permissions

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