Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dynamically Calculating Report Control (2002)

    I have a report with a textbox called Status. The value of Status needs to be determined dynamically based on the values of other fields in the underlying recordset. I try to do this in the Detail_Format event but find that my records are getting the wrong values. When I step through it, I realized that the Status control seems to hold on to its previous value and somehow that eventually wraps around so that my first record gets my last record's value for Status, my second record - the first record's value, etc.. Can anyone tell me how to get my records to hold on to their assigned values for this control? If I need to be more clear, please tell me.

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

    Re: Dynamically Calculating Report Control (2002)

    Is it really necessary to use code to calculate the value of Status? If you could calculate it in the control source of the Status text box, or in the record source of the report, you would get the correct value. Otherwise, you'll have to provide more details about the code you are using.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamically Calculating Report Control (2002)

    I would love to calculate the value in the control source. I have tried many ways to achieve this but so far, no luck. The calculation that I need is as follows:

    If Expired = true then
    me.status = "EXP"
    ELSE
    If isnull(dischargeDate) then
    me.status="A"
    else
    me.status = "DC"
    end if

    I have tried to do this using a nested IIF in the control source but I only got #Error on my report. As I said, I have this code in the detail_format event of my report and it's messing up royally.
    end if

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

    Re: Dynamically Calculating Report Control (2002)

    If Expired and DischargeDate are both fields in the record source of the report, you should be able to use this as control source:

    =IIf([Expired],"EXP",IIf(IsNull([DischargeDate]),"A","DC"))

    If Expired or DischargeDate is not in the record source, where does it come from?

  5. #5
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamically Calculating Report Control (2002)

    Expired and DischargeDate are in the record source. I tried the IIF that you suggested and I got #Error as the value of my status field. However, I will have another look at the report and try it again. Thanks for the help, and I'll let you know what happens!

  6. #6
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamically Calculating Report Control (2002)

    Thank you Hans. I don't know what I was doing incorrectly before, but it works now using the nested IIF. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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